Arrays and Lists in SQL Server 2008

PublishedJanuary 30, 2011 UpdatedJanuary 31, 2011

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

CREATE PROCEDURE [dbo].[sp_XmlParamSample]
(
	@idXml XML
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @handle INT

	EXEC sp_xml_preparedocument @handle OUTPUT, @idXml

	SELECT * FROM MyTable
	WHERE
	Id IN (
		SELECT * FROM OPENXML
		(@handle, '/ArrayOfInt', 2) WITH (int INT)
	)

	EXEC sp_xml_removedocument @handle
END