Microsoft® SQL Server 2008 R2 Unleashed

Table-Valued Parameters

In previous versions of SQL Server, it was not possible to share the contents of table variables between stored procedures. SQL Server 2008 changes that with the introduction of table-valued parameters, which allow you to pass table variables to stored procedures as input parameters. Table-valued parameters provide more flexibility and, in many cases, better performance than temporary tables as a means to pass result sets between stored procedures.

To create and use table-valued parameters, you must first create a user-defined table type as a TABLE data type and define the table structure. This is done using the CREATE TYPE command, as shown in Listing 42.10.

Listing 42.10 Defining a User-Defined Table Type

After creating ...