SQL Server Stored Procedure input parameters use table values

If we want to pass the table as an input parameter to the SQL Server Stored Procedure before 2008, we may need a lot of logic processing to pass the table data as a string or XML.

 

Table value parameters are provided in 2008. Using Table value parameters, you can send multiple rows of data to a Transact-SQL statement or routine (such as a stored procedure or function) without having to create temporary tables or many parameters, this saves a lot of custom code. Such operations are very easy to operate on table functions in stored procedures.

Table value parameters are declared using user-defined table types. Therefore, you must first define the table type before use.

/* Create a table type .*/

Create type locationtabletype
As Table

(Locationname
Varchar (50)

, Costrate
INT );

Go

 

/* Create a stored procedure with the table value parameter as the input .*/

Create procedure DBO. usp_insertproductionlocation

@ TVP locationtabletype readonly

As

Set
Nocount on

Insert
Into production. Location

(Name

, Costrate

, Availability

, Modifieddate)

Select
*, 0, getdate ()

From @ TVP;

Go

 

 

/* Declare the table value parameter variable .*/

Declare @ locationtvp
Aslocationtabletype;

 

/* Insert data into the table value variable */

Insert into @ locationtvp (locationname, costrate)

Select name, 0.00

From person. stateprovince;

 

/* Pass the variable to the stored procedure */

Exec usp_insertproductionlocation @ locationtvp;

Go

 

Query the table production. location to see that the data has been inserted.

For more information about the advantages and disadvantages, see use table value parameters (database engine)