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
Add a comment
|
4 Answers
For a number of different ways to do this, please see:
1 Comment
Faizan Dosani
Thanks for your response, seems like we are unable to pass array directly but can achieve desired goal through many alternatives mention in above link
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)
3 Comments
OMG Ponies
Problem with using XML is that the markup eats the characters you can submit, because the markup is characters as well :/
Adriaan Stander
Hye, how are you, havent seen you in some time. Yes that is unfortunately true X-(
OMG Ponies
I'm doing my best to keep ahead of you, but it's hard work =)
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
Comments
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.
