How to use Table-Valued Parameters in sql?

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.

Advantage:

• Do not acquire locks for the initial population of data from a client.
• Provide a simple programming model.
• Enable you to include complex business logic in a single routine.
• Reduce round trips to the server.
• Can have a table structure of different cardinality.
• Are strongly typed.
• Enable the client to specify sort order and unique keys.
• Are cached like a temp table when used in a stored procedure. Starting with SQL Server 2012, table-valued parameters are also cached for parameterized queries.

Disadvantage:

• SQL Server does not maintain statistics on columns of table-valued parameters.
• Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
• You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.
•  Compared to bulk operations that have a greater startup cost than table-valued parameters, table-valued parameters perform well for inserting less than 1000 rows.

Code:

/* Create a table type. */ CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT ); GO /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE dbo. usp_InsertProductionLocation @TVP LocationTableType READONLY AS SET NOCOUNT ON INSERT INTO AdventureWorks2012.Production.Location (Name ,CostRate ,Availability ,ModifiedDate) SELECT *, 0, GETDATE() FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @LocationTVP AS LocationTableType; /* Add data to the table variable. */ INSERT INTO @LocationTVP (LocationName, CostRate) SELECT Name, 0.00 FROM AdventureWorks2012.Person.StateProvince; /* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO