Passing variable numbers of parameters to sprocs using XML

Passing a variable number of parameters to a stored procedure is a problem that’s been around and solved for a while – in fact there’s a good article on several approaches for passing parameters in a comma-delimited string here:

 

http://www.sommarskog.se/arrays-in-sql.html

 

Today I’d like to talk about an alternative approach made possible in SQL Server 2005 – passing parameters with the XML data type.

 

Let’s imagine that we have a table of employees with an EmployeeID. We want to select some arbitrary subset of this table by passing in a list of the EmployeeIDs of the desired employees.

 

The XML we are going to pass will look something like this:

 

<employees><employee id=”1” /><employee id=”3” />…..</employees>

 

This can be built up quite simply in .NET using an XmlTextWriter as the following code snippet shows:

 

StringBuilder xmlEmployeeListBuilder = new StringBuilder();

StringWriter stringWriter = new StringWriter(xmlEmployeeListBuilder);

XmlTextWriter xmlWriter = new XmlTextWriter(stringWriter);

xmlWriter.WriteStartDocument();

xmlWriter.WriteStartElement("employees");

 

… other code

 

// add an employee id to xml parameter string

// repeat this section as necessary

xmlWriter.WriteStartElement("employee");

xmlWriter.WriteAttributeString("id", <variable containing id>);

xmlWriter.WriteEndElement();

 

… other code

 

xmlWriter.WriteEndElement();

xmlWriter.WriteEndDocument();

xmlWriter.Close();

string xmlEmployeeList = xmlEmployeeListBuilder.ToString();

 

Now let’s look at the stored procedure in full and then break it down:

 

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

 

CREATE PROCEDURE SelectSpecificEmployees

      @EmployeeList xml

AS

SET NOCOUNT ON;

 

CREATE TABLE #EmployeeList

(

      EmployeeId int

);

 

INSERT #EmployeeList

SELECT employee.value('.', 'int')

FROM @EmployeeList.nodes('/employees/employee/@id') T(employee);

 

SELECT Employees.EmployeeId, FirstName, LastName

FROM Employees INNER JOIN #EmployeeList ON Employees.EmployeeId = #EmployeeList.EmployeeId;

 

DROP TABLE #EmployeeList;

 

GO

 

The first thing to note is the two SET statements outside the stored procedure declaration. These SET options are different than all other set options in that whenever a stored procedure executes, it uses the settings for QUOTED_IDENTIFIER and ANSI_NULLS that were in place at the time the stored procedure was created!

 

Changing these settings inside a stored procedure has no effect and produces no errors, and neither does the setting at the database level have any effect on the stored procedure.

 

If you don’t have these options set as shown, then when you try and run this stored procedure you’ll get an error like:

 

“INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.”

 

You have been warned!

 

The next step is to create a temporary table to hold the employee ids parsed from the XML.

 

Now we insert the employee ids into the temporary table. To do this, we use the nodes() method of an XML data type. This executes an XQuery expression that returns a nodeset against the xml instance and places the results into a table. The table is scoped to the SQL statement in which it is declared.

 

In the example above, we declare a table called T with a column of employee, and insert the rows from table T into our temporary #EmployeeList table.

 

That’s all the hard work done – now we just join our #EmployeeList to the actual Employees table and return the matching employees.

 

Comparing this approach to the amount of code required to deal with comma-delimited strings, I think this is a neater, shorter solution to the problem. How about you?