Passing array as Parameter to SQL 2005 stored procedure

Question

Hi All,

How to pass array as parameter to a Store Procedure in SQL Server 2005, i this is not possible SQL server 2000, so we pass comma separated values in past.

any help appreciated

Answer 1

This is still the case with Sql Server 2005.

You make use of the XML type

XML Support in Microsoft SQL Server 2005

Here is an example of how to split a string into rows using the XML data type

DECLARE @textXML XML
DECLARE @data NVARCHAR(MAX), 
     @delimiter NVARCHAR(5)

SELECT  @data = 'A,B,C',
     @delimiter = ','

SELECT    @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML)
SELECT  T.split.value('.', 'nvarchar(max)') AS data
FROM    @textXML.nodes('/d') T(split)

Answer 2

For a number of different ways to do this, please see:

Arrays and Lists in SQL Server 2005

Answer 3

Instead of a CSV, you could send an XML and then parse it in the procedure. For an example, look at Passing lists to SQL Server 2005 with XML Parameters

Answer 4

See Passing an array or DataTable into a stored procedure for a method of passing arrays as image / varbinary objects and parsing them in the SP which is possible in SQL 2000 as well.