MCTS 70-433 Table-Valued Parameters

The use of Table-Valued Parameters is slightly advanced, but they are strongly typed (safer / better design) and can be a useful tool for advanced programmers:

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

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.

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.

http://msdn.microsoft.com/en-us/library/bb510489.aspx