Home > Uncategorized > Table-Valued parameter in SQL Server 2005

Table-Valued parameter in SQL Server 2005

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.

About these ads
  1. VAL
    February 24, 2009 at 10:34 pm | #1

    Hey Misbah,This is a great post – saved me some time for sure. There is one drawbach though. Your the EmployeeXML string contans the xml with this nodes <E EmployeeID="1">. When you create a DataTable in memory and then try to serialize it with dt.WriteXML() the resulting xml will be as following: <DocumentElement> <E><EmployeeID>1</EmployeeID></E> <E><EmployeeID>2</EmployeeID></E> <E><EmployeeID>3</EmployeeID></E> <E><EmployeeID>4</EmployeeID></E></DocumentElement>and so on.In this situation the query in the stored procedure will not work:SELECT E.* FROM HumanResources.Employee E INNER JOIN @EmployeeList.nodes(\’/DocumentElement/E/EmployeeID\’) AS Tbl(C) ON E.EmployeeID = Tbl.C.value(\’@EmployeeID\’, \’INT\’);Can suggest a workaround?Thanx

  2. Misbah
    February 26, 2009 at 11:10 am | #2

    just change your query in SQL to something like: SELECT E.* FROM HumanResources.Employee E INNER JOIN @EmployeeList.nodes(\’/DocumentElement/E/EmployeeID\’) AS Tbl(C) ON E.EmployeeID = Tbl.C.value(\’.\’, \’INT\’);sorry the sample proc i posted was for the commented out string EmployeeXml :(

  3. Dave
    June 15, 2011 at 1:50 pm | #3

    To make WriteXml() output values as attributes instead of child elements, do this on the DataColumn:

    dc.ColumnMapping = MappingType.Attribute;

    To change the name of the root element, add the DataTable to a DataSet created with the root name:

    DataSet ds = new DataSet(“Root”);
    DataTable dt = new DataTable(“T”);
    ds.Tables.Add(dt);

    Thanks for this article, I found it easier to understand than many other articles on this topic.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: