What’s new in SQL 2008 

Perquisites: You should have SQL 2008 and .NET Framework 4

Table-Valued Parameters

 

1) Create your data type first,

The following statement creates a table type named CategoryTableType that consists of CategoryID and CategoryName columns:

CREATE TYPE dbo.CategoryTableType AS TABLE

    ( CategoryID int, CategoryName nvarchar(50) )

2) Create a stored procedure, which will cosume this newly created table 'type'

After you create a table type, you can declare table-valued parameters based on that type. The following Transact-SQL fragment demonstrates how to declare a table-valued parameter in a stored procedure definition. Note that the READONLY keyword is required for declaring a table-valued parameter.

CREATE PROCEDURE usp_UpdateCategories

    (@tvpNewCategories dbo.CategoryTableType READONLY) 

/*

 -- You can use this @tvpNewCategories table for insert/update/select joins, anything as a normal table

*/

UPDATE dbo.Categories

    SET Categories.CategoryName = ec.CategoryName

    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec

    ON dbo.Categories.CategoryID = ec.CategoryID;

3) Passing table from code to stored procedure

 // Assumes connection is an open SqlConnection object.

using (connection)

{

// Create a DataTable with the modified rows.

DataTable addedCategories =

  CategoriesDataTable.GetChanges(DataRowState.Added);

// Configure the SqlCommand and SqlParameter.

SqlCommand insertCommand = new SqlCommand(

    "usp_InsertCategories", connection);

insertCommand.CommandType = CommandType.StoredProcedure;

SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(

    "@tvpNewCategories", addedCategories);

tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.

insertCommand.ExecuteNonQuery();

}

Limitations

1)      You cannot pass table-valued parameters to user-defined functions.

2)     Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server does not maintain statistics on table-valued parameters.

3)     Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.

4)      You cannot use ALTER TABLE statements to modify the design of table-valued parameters.