Pass default value for Table Valued parameter - SQL Server

Question

I use the table-value parameter in one of our stored procedures. Here is the syntax I used:

@districtlist NumericList readonly 

( NumericList is a custom table type).

However, as a requirement, I need to pass the default values ​​to this parameter specified in the table.

 @districtlist NumericList = 0 readonly 

But the above code generates a syntax error. Is it possible to pass the default value to a table parameter? Can someone help me with this?

Answer 1

You can refuse a parameter even if it has no default value. For example:

 CREATE TYPE TestTable AS TABLE (my_id INT) GO CREATE PROCEDURE dbo.Test_TVP @my_table TestTable READONLY, @my_int INT AS BEGIN SELECT * FROM @my_table END GO EXEC dbo.Test_TVP @my_int = 2 GO DROP PROCEDURE dbo.Test_TVP DROP TYPE TestTable 

In this case, the table will be just empty. If you need a certain number of rows by default, you will need to simulate this in a stored procedure, possibly using a temporary table, since the table parameters must be READONLY .

Answer 2

You can pass TVP by default:

 EXEC dbo.Test_TVP @my_table = default 

Which is equivalent to an empty table.