Udostępnij za pośrednictwem

Passing variable numbers of parameters to sprocs using XML

  • Artykuł

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:

https://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?

Comments

  • Anonymous
    February 22, 2007
    James, I do prefer this approach and would like to apply it in our system. As part of the ASP.NET applications we run there is a configurable input form where a variable number of inputs can be displayed to the user and ultimately stored on our SQL Server 2000 DB. All data is stored in one generic table while validation is done on an input-by-input basis in server side C#. Currently, after all data has passed validation we loop thru each input in C# and repeatedly call the Stored Proc to simply insert/update/delete the data point on the database. Some forms run into 30-40 fields. It may be inefficient but the configurable nature of the application takes precedence. To improve efficiency, rather than many calls on the DB to submit data could I transform the data to XML using your suggested method using SQL Server 2000? (We are looking to move to 2005 this year) Following on from this, and In respect of either 2000 or 2005 versions, is there a neat way of submitting many datapoints in XML format into 1 table or are we constrained to the looping around a temporary table? cheers, Mike

  • Anonymous
    March 19, 2007
    The comment has been removed