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