Welcome to weblogs.com.pk Sign in | Join | Help

Passing Array To Stored Procedures

There are some scenarios; where you want to pass array to stored procedure. In SQL 2000; it was not possible. Different work-around was presented; like passing values as string and then parsing them out; passing data as XML and then using MSXML OLE automation object in SQL Server (sp_OACreate/sp_OADestroy). Both have the pit falls; parsing strings is not very efficient, OLE Automation is not available when SQL Server Kernel is running using NT Fibers (my observation I might be wrong)

With SQL Server 2005; we now have native support of XML. Yesterday my colleague asked about passing array to stored procedure and I remarked that it should be possible now using XML. I myself had not yet explored XML support in SQL so I couldn’t give him examples or references. Later in night; I decided to try it out. It was my first introduction to Yukon's XML support. Here is the sample stored procedure for passing arrays as XML document.

alter procedure XmlTest (@xml xml) as
   
select t2.d.value('.', 'varchar(50)')
    from @xml.nodes('/root/a') t2(d)
go
declare @@xml xml;
set @@xml = '<root><a>111</a><a>222</a></root>';
exec XmlTest @@xml;

Some more TSQL for my reference

declare @x xml;
set @x = '<root><a>111</a><a>222</a></root>';
select t2.d.value('.', 'varchar(50)'), --will show 111, 222 (string)
   
t2.d.query('.'), --will show <a>111</a>, <a>222</a> (xml)
   
t2.d.query('text()') --will show 111, 222 (xml)
from @x.nodes('/root/a') t2(d)

Please note, ADO.NET v2 has support of XML data type. I haven’t yet tried it; so cannt say much.

Published Wednesday, March 08, 2006 1:17 AM by khurram
Filed under: ,

Comments

# re: Passing Array To Stored Procedures

Wednesday, March 08, 2006 1:54 AM by aziz
Microsoft has incorporated many things from Oracle to Sql2005 this time. Like ROWNUM , like passing arrays to sp etc etc . Microsoft is good in it.
New Comments to this post are disabled