Archive

Posts Tagged ‘Table-Valued Parameters’

Table-Valued parameter in SQL Server 2005

August 3, 2008 3 comments

In pre-SQL Server 2005 in order to pass in a set of values one had to create a temporary table, populate it with data using INSERT, and then just use it in the procedure or function since they are created for the current session and are available to all processes in that session.

I did a blog on how to pass in Table-Value Parameters in SQL Server 2008 but what if we have a need to pass in multiple rows of data to a T-SQL statement, or a routine such as stored procedure or function in SQL Server 2005?

Turns out the same can be done in SQL Server 2005 without using temporary tables. By using the XML data type you can pass user-defined sets between queries and also between the client and the server.

The following code shows how you can create and use XML parameters.

USE AdventureWorks; GO CREATE PROCEDURE uspEmployeeList(@EmployeeList XML) AS BEGIN SET NOCOUNT ON; SELECT E.* FROM HumanResources.Employee E INNER JOIN @EmployeeList.nodes('/Root/E') AS Tbl(C) ON E.EmployeeID = Tbl.C.value('@EmployeeID', 'INT'); RETURN; END
 

How are XML parameters supported in .NET? ADO.NET allows full support using SqlDbType.Xml type. Adding a XML as a parameter to the stored procedure from C# would have something like:

//string EmployeeXml = "<Root><E EmployeeID=\"1\" /><E EmployeeID=\"3\" /><E EmployeeID=\"5\" /><E EmployeeID=\"7\" /><E EmployeeID=\"11\" /></Root>";
 
// Create the data table
DataTable dt = new DataTable("E");
// Create the table schema
DataColumn dc = dt.Columns.Add("EmployeeID", typeof(string));
// Add a few records to the data table.
for (int i = 1; i <= 10; i++)
{
      // create a new row
      
DataRow
dr = dt.NewRow();
      // populate the fields
      
dr[0] = i.ToString();
      // add the row to the table
      
dt.Rows.Add(dr);
}
... System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("uspEmployeeList", sqlConn); cmd.Parameters.Add("@EmployeeList", System.Data.SqlDbType.Xml); //cmd.Parameters["@EmployeeList"].Value = EmployeeXml; ...
// Create a temporary MemoryStream to hold the output of the WriteXml method of the DataTable
using (MemoryStream memoryStream = new MemoryStream())
{
dt.WriteXml(memoryStream);
         UTF8Encoding encoding = new UTF8Encoding();
         cmd.Parameters["@EmployeeList"].Value = encoding.GetString(memoryStream.ToArray());
}

Now that’s cool. This is much better than passing in a comma separated list and using a dynamic query in our procedure or function.

Advertisements

Table-Value Parameters in SQL Server 2008

April 26, 2008 2 comments

One of the cool new data types added in SQL Server 2008 is Table-Value Parameters. You can use table value parameters to send multiple rows of data to a Transact-SQL statement, or to a routine such as a stored procedure or function. This can be done without creating a temporary table or many parameters. Table-value parameters are like parameter arrays in Object Linking and Embedding Database (OLE DB) and Open Database Connectivity (ODBC), but offer better flexibility and closer integration with Transact-SQL. Table-value parameters can participate in set-based operations.

Table-value parameters allow you to pass user-defined tables between queries and also between the client and the server. In SQL Server 2008, you can use table-value parameters to pass an entire set of data into functions and procedures.

In earlier versions of SQL Server in order to pass in a set of values one had to create a temporary table, populate it with data using INSERT, and then just use it in the procedure or function since they are created for the current session and are available to all processes in that session.

With table-value parameters you need to declare a table variable, populate it with data using standard routines such as INSERT, and then pass it into a procedure or a function. The database engine handles this data by using a reference marked with the READONLY property to ensure that the data does not get copied again.

Table-value parameters have two primary components – a SQL Server type and a parameter. The following are the steps to create and use table-value parameters:

  1. Create a table type and define the table structure.
  2. Declare a routine that has a parameter of the table type.
  3. Declare a variable of the table type and then reference the table type.
  4. Fill the table variable by using an INSERT statement.
  5. Pass the variable to a routine.

The following code shows how you can create and use table-value parameters.

/* Create a table type. */ CREATE TYPE DeptTableType AS TABLE( DeptName VARCHAR(50), DeptCode VARCHAR(2)); GO /* Create a procedure to receive data for the table-value parameter.*/ CREATE PROCEDURE uspDepartmentInsert @TVP DeptTableType READONLY AS SET NOCOUNT ON; INSERT INTO Dept(Name, DeptCode, ModifiedDate) SELECT *, GETDATE() FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @DeptTVP AS DeptTableType; /* Add data to the table variable. */ INSERT INTO @DeptTVP(DeptName, DeptCode) VALUES('Software Development', 'SD'); INSERT INTO @DeptTVP(DeptName, DeptCode) VALUES('Quality Assurance', 'QA'); /* Pass the table variable data to a stored procedure. */ EXEC uspDepartmentInsert @DeptTVP; GO

Table-value parameters offer more flexibility and better performance than temporary tables to pass a list of parameters. Table-value parameters do not acquire locks for the initial population of data from a client and do not cause a statement to recompile.

This is all great but in any real application the parameters are usually passed in from .NET code. How are table-value parameters supported in .NET? ADO.NET 3.0 allows full support using SqlDbType.Structured type. The parameters which can be passed in from ADO.NET are DataTables, IEnumerable<SqlDataRecord> and DbDataReader. There is also support for ODBC, SSMS, and within Replication.

Adding a DataTable as a parameter to the stored procedure from C# would have something like:

DataTable dt = new DataTable(); dt.Columns.Add.... dt.Rows.Add..... SqlCommand cmd = new SqlCommand("uspDepartmentInsert", sqlConn); cmd.Parameters.AddWithValue("@DeptTVP", dt); cmd.ExecuteNonQuery();

Now that’s cool.

Table-value parameters offer the following benefits:

  • Provide a simple programming model.
  • Enable inclusion of complex business logic in a single routine.
  • Reduce round trips to the server.
  • Include a table structure of different cardinality.
  • Enable strongly typed and set-oriented queries.
  • Enable the client to specify sort order and unique keys.

Table-value parameters have the following restrictions:

  • Statistics are not maintained on columns of table-value parameters.
  • Table-value parameters must be passed as input READONLY parameters to Transact-SQL routines.
  • DML operations such as UPDATE, DELETE, or INSERT cannot be performed on a table-value parameter in the body of a routine. This may actually be a good thing if the data is not meant to change and a read-only copy of the data is all that is needed. However, if the routine needs to update the rows then you’d have to use temporary tables instead in procedures.
  • Table-value parameters cannot be used as the target of a SELECT INTO or INSERT EXEC statement. Table-value parameters can be in the FROM clause of SELECT INTO, or in the INSERT EXEC string or stored procedure.