Wednesday, April 06, 2011

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

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.

No comments: