Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pass Default value to Table Valued parameter - SQL Server

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?

like image 255
bmsqldev Avatar asked Mar 22 '16 12:03

bmsqldev


People also ask

How do you pass a table as a parameter in SQL?

Create a user-defined table type that corresponds to the table that you want to populate. Pass the user-defined table to the stored procedure as a parameter. Inside the stored procedure, select the data from the passed parameter and insert it into the table that you want to populate.

How do you create a table valued parameter in SQL Server?

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.

Can table valued parameter be null?

Hope it helps. As the User Define table Types are created as table-valued, so you cannot assign null to a table. Hope it helps.


2 Answers

You can pass the TVP as default:

EXEC dbo.Test_TVP @my_table = default

Which is the equivalent of an empty table.

like image 186
Remi Lemarchand Avatar answered Sep 21 '22 17:09

Remi Lemarchand


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.

like image 23
Tom H Avatar answered Sep 22 '22 17:09

Tom H