Arrays and Lists in SQL Server 2008

I sure hope this article will help solve a problem for me:” . It sure looks promising since the opening paragraph pretty much asks my question and the remainder of the article addresses it with solutions. I would like to pass an array of id’s as a single parameter to a stored procedure. Another solution to consider is passing the id’s as an XML string, and then parse the XML into a temp table to join against, or a query against the XML directly using SP_XML_PREPAREDOCUMENT and OPENXML.

Stay tuned and I will let you know how it turns out…

UPDATED Jan 31, 2011

Well, I decided to code it using the latter method described above. Here is some sample code to illustrate:

//create a list of id's for the XmlSerializer
List ids = new List();

//create Xml doc from list to pass id's into sproc as a single param
XmlSerializer xs = new XmlSerializer(typeof(List));
MemoryStream ms = new MemoryStream();
xs.Serialize(ms, ids);
string idXml = UTF8Encoding.UTF8.GetString(ms.ToArray());
idXml created from the above c# code:
<?xml version="1.0" ?> 
<ArrayOfInt xmlns:xsi="" xmlns:xsd="">

Stored Procedure

Published by

Jim Frenette

Web Developer - views here are my own except those taken from people more clever than me.