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)