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:
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