Menu
Is it possible to create a procedure with a table value parameter as an optional parameter.
I tried the following code:
CREATE PROCEDURE SP @Table testteype = null READONLY AS .... But I get this error:
Operand type clash: void type is incompatible with test type ps: I use sql server with C#.Net
525
Answers. No, TVP can not be null, but it may be a table with no records.
A parameter is considered optional if the parameter has a default value specified when it is declared. It is not necessary to provide a value for an optional parameter in a procedure call. The default value of a parameter is used when: No value for the parameter is specified in the procedure call.
Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.
The trick that enables a work around for declaring optional parameters for t-sql functions is checking the parameter with ISNULL() within the sql function definition and calling the function with NULL values where you want to use default value for the optional parameter.
Table-Valued parameters always have an implicit value of an empty table. So you can actually call that procedure without any parameters and it would execute but the table would be empty.
So it doesn't really make sense to label a table-value parameter with a default value. Remove the "=null", check the table contents, and you should be good to go.
50
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
