Arrays and Lists in SQL Server 2008

I sure hope this article will help solve a problem for me: http://www.sommarskog.se/arrays-in-sql-2008.html” . 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:

C#
//create a list of id's for the XmlSerializer
List ids = new List();
ids.Add(109);
ids.Add(120);
ids.Add(203);

//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="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<int>109</int> 
	<int>120</int> 
	<int>203</int> 
</ArrayOfInt>

Stored Procedure

Published by

Jim Frenette

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

Loading Disqus Comments ...
Loading Facebook Comments ...