views:

135

answers:

2

I want to truncate the TVP instead of DROPPING.

When I am writing TRUNCATE TYPE it is giving error.

DROP TYPE is working but iI want TRUNCATE

Can any one help me with the syntax?

thank you

+3  A: 

In SQL Server 2008, a Table Valued Parameter is ReadOnly in the TSQL code.

To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.

Ref.

Mitch Wheat
A: 

The TVP is a table variable of a certain user-defined table type. The type is like a class and the variable like an instance. You can drop the user-defined table type, but then you couldn't use it as a TVP.

As the TVP, the table variable is read-only. In other contexts, you could delete all rows from the table variable, but you can never truncate it. Only user tables and temporary tables can be truncated.

You could copy the TVP into a temporary table and then truncate that, but I'm not sure what use that would be!

Peter