SQL Server uses table values as input parameter examples in stored procedures.

SQL Server uses table values as input parameter examples in stored procedures.

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 using 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.