Pass Default value to Table Valued parameter - SQL Server
Solution 1
You can pass the TVP as default:
EXEC dbo.Test_TVP @my_table = default
Which is the equivalent of an empty table.
Solution 2
You can opt to not pass the parameter, even if no default is defined for it. 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 simply be empty. If you wanted some number of default rows then you would have to simulate that in the stored procedure, probably by using a temporary table since table-valued parameters must be READONLY
.
![bmsqldev](https://i.stack.imgur.com/X4Wgy.jpg?s=256&g=1)
bmsqldev
Business Intelligence Developer with 5 + years of Experience in MS-SQL Server,T-SQL, SSIS, SSRS, SSAS , Tableau ,power bi, COGNOS, AZURE DATAWAREHOUSE
Updated on August 10, 2022Comments
-
bmsqldev almost 2 years
I am using a table-valued parameter in one our stored procedures. Here is the syntax I used:
@districtlist NumericList readonly
(
NumericList
is the user-defined table type).However, as a requirement I need to pass default values to this table valued parameter.
@districtlist NumericList = 0 readonly
But above code throws a syntax error. Is it possible to pass default value to a table valued parameter? Could someone help me on this?
-
Tom H over 8 yearsIf you try to pass it as
NULL
then it will fail. For example,@my_table = NULL
will give you an error thatNULL
is incompatible with your table data type. -
bmsqldev over 8 yearswhat i meant is , in c# code i just use null or 0 in the insert list and pass the list into sql code.
-
Oleksandr over 3 yearsThis is only way to pass it to a UDF.