Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Table-Valued parameters in SQL Server 2008

Introduction

The most awaited feature has arrived - "Table-Valued parameters in SQL Server 2008". Here, I would discuss on how to use this new feature with .net.

Problem Statement
Before to the introduction of table-valued parameters in SQL Server 2008, the options for passing data from multiple rows of a DataTable to a stored procedure or a parameterized SQL command were limited. Some of the possible options for a developer until now were:
1. Make multiple parameters, with one parameter representing one column of the DataTable
2. Create XML string & then parse it in the SP
3. Create a delimiter separated string & then parse it in the SP

Also, the above stated options had to be repeated for each row....hhuuuuuuuuhh.......just imagine the round trips to the server for large DataTables or the complex logic to be implemented for parsing the parameters.

Solution with Table-Valued parameter

1. Creating Table-Valued Parameter Types
CREATE TYPE dbo.TableType AS TABLE

( Col1 int, Col2 nvarchar(50),Coln ... )

2. Create Stored Procedure with a table valued parameter
CREATE PROCEDURE dbo.StoredProc

(@TableType dbo.TableType READONLY)

 NOTE : The READONLY keyword is required for declaring a table-valued parameter.

3. Use the parameter as under -
For Insert

INSERT INTO dbo.Tbl (Col1,Col2)

SELECT paramTbl.Col1, paramTbl.Col2

FROM @TableType AS paramTbl;

 

For Update
UPDATE dbo.Tbl

SET Tbl.Col2 = paramTbl.Col2

FROM dbo.Tbl

INNER JOIN @TableType AS paramTbl

ON dbo.Tbl.Col1 = paramTbl.Col1;


For Delete
DELETE FROM dbo.Tbl

FROM dbo.Tbl

INNER JOIN @TableType AS paramTbl

ON dbo.Tbl.Col1 = paramTbl.Col1;


4. Calling from .net Code

// Assumes connection is an open SqlConnection object.

using (connection)

{

// Create a DataTable with the modified rows.

DataTable addedValues = ValuesDataTable.GetChanges(DataRowState.Added);

 

// Configure the SqlCommand and SqlParameter.

SqlCommand insertCommand = new SqlCommand("SP_Name", connection);

 

insertCommand.CommandType = CommandType.StoredProcedure;

 

SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@TableType ", addedValues );

tvpParam.SqlDbType = SqlDbType.Structured;

 

// Execute the command.

insertCommand.ExecuteNonQuery();

}


Limitations of Table-Valued parameter
1. User-defined functions do not support Table valued parameters.
2. Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints.
3. SQL Server does not maintain statistics on table-valued parameters.
4. 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.
5. You cannot use ALTER TABLE statements to modify the design of table-valued parameters.
6. If you are using Entity Framework 1.0, DBType enum does not support Structured type.

As a work around, create a SQLParameter & then typecast it into DbParameter.


SqlParameter param = new SqlParameter("@TableType", addedValues );

param.SqlDbType = SqlDbType.Structured;

command.Parameters.Add(param as DbParameter);

Reference
1. 
http://msdn.microsoft.com/en-us/library/bb675163.aspx

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.