Table-Valued Parameters (Database Engine)

Creating and Using Table-Valued Parameters in Transact-SQL

Table-valued parameters have two primary components: a SQL Server type and a parameter that references that type. To create and use table-valued parameters, follow these steps:

1. Create a table type and define the table structure.

   For information about how to create a SQL Server type, see User-Defined Table Types. For more information about how to define a table structure, see CREATE TABLE (Transact-SQL).

2. Declare a routine that has a parameter of the table type. For more information about SQL Server routines, see CREATE PROCEDURE (Transact-SQL) and CREATE FUNCTION (Transact-SQL).

3. Declare a variable of the table type, and reference the table type. For information about how to declare variables, see DECLARE @local\_variable (Transact-SQL).

4. Fill the table variable by using an INSERT statement. For more information about how to insert data, see Adding Rows by Using INSERT and SELECT.

5. After the table variable is created and filled, you can pass the variable to a routine.

   After the routine is out of scope, the table-valued parameter is no longer available. The type definition remains until it is dropped.

To use a table-valued parameter in the SQL Server Native Client, see Table-Valued Parameters (SQL Server Native Client).

To use a table-valued parameter in ADO.NET, see the ADO.NET documentation.

Benefits

Restrictions

Scope

Security

Catalog Views

Table-Valued Parameters vs. BULK INSERT Operations

Using table-valued parameters is comparable to other ways of using set-based variables; however, using table-valued parameters frequently can be faster for large data sets. 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.

Table-valued parameters that are reused benefit from temporary table caching. This table caching enables better scalability than equivalent BULK INSERT operations. By using small row-insert operations a small performance benefit might be gained by using parameter lists or batched statements instead of BULK INSERT operations or table-valued parameters. However, these methods are less convenient to program, and performance decreases quickly as rows increase.

Table-valued parameters perform equally well or better than an equivalent parameter array implementation.

The following table shows which technology to use based on the speed of insert operations.

Examples

The following example uses Transact-SQL and shows you how to create a table-valued parameter type, declare a variable to reference it, fill the parameter list, and then pass the values to a stored procedure.

USE AdventureWorks;
GO

/* 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 usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks].[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 
    [AdventureWorks].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

See Also

Reference