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