Dale Preston's Web Log: The Second-to-Last How-to-Pass-Arrays-to-Stored-Procedures Article You will Ever Need

Wednesday, December 12, 2007

The Second-to-Last How-to-Pass-Arrays-to-Stored-Procedures Article You will Ever Need

The Problem
What About CLR?
The XML Solution
Where's the Array?
Summary

The Problem

It is often desirable to pass an array of values to a SQL Server stored procedure. Perhaps you want to make a similar change to multiple rows in a database. If the rows are sequential by the primary key, you can use BETWEEN. If the rows you want to change are either unordered or random, then you either have to call a procedure multiple times, passing the key to one row with each call, or attempt to pass the unique key value for all rows as an array. Unfortunately, though, SQL (in all versions I know of, not just SQL Server) does not have any provisions for an array data type.

That's not a problem, though. Many clever SQL developers have come up with nearly as many different ways of passing arrays to a stored procedure in SQL Server. Most methods involve some sort of delimited string passed as a text or varchar parameter like this pipe-delimited string account numbers from the AdventureWorks sample database:

'AW00000158|AW00000432|AW00000518|AW00000694|AW00000701'

Then the developer would creat a loop which would pick the account numbers one at a time from the beginning of the string to the end.

Solutions to the problem have been covered in blogs, web articles many times. That is why, when I needed to pass an array to a stored procedure today, I started with a Google search on the subject. It's not that I didn't know how to do it, I just figured I would find something quick that I could copy-and-paste and save a lot of keystrokes. I got a little excited when I found a match to my search in a Microsoft Knowledge Base article. I thought maybe there would be something new there that I hadn't seen before, considering I didn't remember ever seeing anything on the topic from Microsoft before.

And I was right. The article, How to pass array of values into SQL Server stored procedure using XML and Visual Basic .NET looked very promising and innovative. Rather than the usual delimited string and a loop the article promised to demonstrate passing the array using XML and the OPENXML command available in versions of SQL Server 2000 and later - yes, I had missed this solution for 7 years. The problem is that the article completely fails to deliver on the promise contained in the title. The article contains a lot of lengthy and confusing code that completely obfuscates the intended demonstration. I could only find one reference to an array in the article and that was an array of SqlParameters. I certainly could not find anwhere in the sample code where an array passed to a stored procedure.

So, hopefully this article will simplify and answer the question that led you and me here.

What About CLR?

This article is the "second-to-last" article you will ever need on this subject because this article does not include one great new option with SQL Server 2005 and later. Using CLR integration, you can pass a .Net array to a CLR procedure and call a loop on that array with excellent performance and only a single round trip to the database server.

The project that led me to my search today, while being SQL Server 2005 based, does not have CLR integration as an option and that is why you may still need one more article on passing arrays to stored procedures.

This article demonstrates how to use XML to pass an array of values to a stored procedure but it is not an article about XML or XML in SQL Server. For more information about either topic, including specifics on the usage of the commands demonstrated, I suggest searching MSDN or SQL Server BOL.

The XML Solution

So, to pass the array to the stored procedures, we are going to use the OPENXML command. The OPENXML command requires a handle to an XML document and an xpath expression.

So first, let's look at some test code. The following SQL statements will generate a simple XML document:

declare @doc varchar(2000)

set @doc =
'<Root>
<Customers AccountNumber="AW00000694" />
<Customers AccountNumber="AW00000701" />
<Customers AccountNumber="AW00000518" />
<Customers AccountNumber="AW00000432" />
<Customers AccountNumber="AW00000158" />
</Root>'

Next use OPENXML to load the XML into memory. The following code instructs SQL Server to load the above document into memory using sp_xml_preparedocument. It then demonstrates how to select from the XML as if it were a data table. Finally, the example unloads the XML from memory using sp_xml_removedocument.

@hDoc, in the following example, is a handle to the XML document provided by sp_xml_preparedocument after loading the XML into memory. Be sure to release the memory as shown because SQL Server only uses a small percentage of of its memory for storing XML and could easily run out of memory otherwise.

-- Load the XML into memory
exec sp_xml_preparedocument @hDoc OUTPUT, @doc
--Select data from the table based on values in XML
SELECT AccountNumber FROM OPENXML (@hdoc, '/Root/Customers', 1)
WITH (AccountNumber VARCHAR(10))
-- Unload the XML from memory
EXEC sp_xml_removedocument @hDoc

The query above returns the selected account numbers, as expected:

AccountNumber
AW00000694
AW00000701
AW00000518
AW00000432
AW00000158

and that list can be used in an IN clause:

-- Load the XML into memory
execsp_xml_preparedocument @hDoc OUTPUT, @doc

--Select data from the table based on values in XML
SELECT c.AccountNumber, c.CustomerID, s.Name AS StoreName, s.SalesPersonID
FROM Sales.Customer c
INNER JOIN Sales.Store s
ON c.CustomerID = s.CustomerId
WHERE c.AccountNumber IN (
SELECT AccountNumber FROM OPENXML (@hdoc, '/Root/Customers', 1)
WITH (AccountNumber VARCHAR(10))
)

-- Unload the XML from memory
EXEC sp_xml_removedocument @hDoc

The query results demonstrate that the list of account numbers yielded from the XML drive the results:

AccountNumber	CustomerID	StoreName	SalesPersonID
AW00000158	158	Rural Sporting Goods	289
AW00000432	432	Super Sports Store	275
AW00000518	518	Regional Cycle Shop	289
AW00000694	694	The Accessories Store	277
AW00000701	701	Future Bikes	282

Where's the Array?

Having shown how to use an XML document and OPENXML to past a list of values to an SQL Server stored procedure, the only task remaining is to obtain that XML from an array of values - strings in our case. This is simply a matter of creating the XML using your favorite programming language (C# for me) and passing that XML to the stored procedure:

private string CreateXmlDocForSql(string[] accountNumbers)
{
string xmlDoc = "";
xmlDoc += @"<Root>" + System.Environment.NewLine;

for (int count = 0; count < accountNumbers.Length; count++)
{
xmlDoc += "\t<Customers AccountNumber=\"" + accountNumbers[count] + "\" />" + System.Environment.NewLine;
}

xmlDoc += @"</Root>" + System.Environment.NewLine;
return xmlDoc;
}

Summary

You can also use the procedures described here to pass a list of values to be looped in a cursor. Yes, I know. Cursors are evil. I sure don't want to get the lightning storm brought on by my blog post on identity columns and primary keys by talking about cursors. But just remember, a cursor using the XML passed to the procedure as a source cannot possibly have the locking issues normally associated with cursors.

I find this to be a much cleaner method of passing an array of values into a SQL query of any type than to parse a delimited string in a loop. Hopefully it will help you, too.

- posted by Dale Preston @ 12/12/2007 09:42:00 PM

Comments:

Hi Dale:
I found your blog through the Microsoft community site for problems with WMP. I signed-up but, for some reason, I attempt to enter a new question and it keeps
re-directing me back to the same page. (using IE7, pop-up blocker off)
If you have the time, maybe you can help me with a problem that I'm having with
WMP11. I recently purchased a Sony MP3 player (NWZ-A818) which came with WMP11 for purposes of setting up a library of my music and assembling playlists. Apparently
WMP11 can "see" the files on the MP3 for "sync" purposes but not for building a playlist. The device's icon appears but I can not open it to see folders. In fact, the instructions from Sony are to create the playlist by pointing to files on my C drive, then connect the MP3, and then sync it up. The problem is that WMP11 sends the playlist and all the .mp3 files along with it, even though they're already present on the device. Tech support at Sony said that I should delete the files on the MP3 before I make a playlist so that I don't end-up with duplicate files. The old saying "they can put a man on the moon...." comes to mind. Am I doing something wrong? I like WMP and would like to continue using it to play media on my laptop. Maybe there's a work-around for this problem. I'm using XP Pro sp2. Any assistance you can offer would be greatly appreciated.

Best regards,

Dan

# posted by

Danny1AE : January 23, 2008 11:19 PM