Posts Tagged ‘SQL Server 2005’

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
dr = dt.NewRow();
      // populate the fields
dr[0] = i.ToString();
      // add the row to the table
... 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())
         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.