Table-Valued Parameters in SQL Server 2008R2

Table-valued parameters are a new parameter type in SQL Server 2008. 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.

Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table-valued parameters also have the benefit of being able to participate in set-based operations. Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data.

You can create and execute Transact-SQL routines with table-valued parameters, and call them from Transact-SQL code, managed and native clients in any managed language.

Benefits

Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Table-valued parameters offer the following benefits:

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

Restrictions

Table-valued parameters have the following restrictions:

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

Scope

A table-valued parameter is scoped to the stored procedure, function or dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. You can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.

Security

Permissions for table-valued parameters follow the object security model for SQL Server, by using the Transact-SQL keywords: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION, and REVOKE.

USE AdventureWorks2008R2; 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 [AdventureWorks2008R2].[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     [AdventureWorks2008R2].[Person].[StateProvince]; /* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO