Ever have the need to pass an array of data to a stored procedure in SQL Server 2005? There are different ways to handle this, but one of the easiest is passing an XML datatype parameter.
I have heard some say it is complicated, but with SQL Server 2005 it is a breeze.
Recently, I had the need to pass an array of ints to a SP where it would act about the data in the database keyed on the int IDs I passed in. Since I could have anywhere from a couple IDs up to a large number, it was simple to choose XML.
Here is a sample and how to pass a simple int array in XML:
This is the stored procedure that will return a 'select' of the array passed to it in XML -
create procedure playXml(@XmlText XML)
as
SELECT tab.num.value('.','int') as IntValue
FROM @XmlText.nodes('IntValues/IntValue') tab(num)
Here is a sample of how to call it in a query:
DECLARE @XMLText XML
SET @XMLText ='
<IntValues>
<IntValue>10</IntValue>
<IntValue>1</IntValue>
<IntValue>-29</IntValue>
<IntValue>887273</IntValue>
<IntValue>234</IntValue>
<IntValue>1</IntValue>
<IntValue>1314</IntValue>
<IntValue>156</IntValue>
</IntValues>'
execute playXml @XmlText
It is quite a simple matter to pass the XML field. Building the query string for a basic array like this is easy. In which ever technology you use, it is no harder than executing the stored procedure.