Passing an Array of Values to a Stored Procedure in SQL Server 2005

I've always dreaded handling the situation where I need to pass an array of values to a stored procedure.  Yeah...there are several ways to do it, but none of them are really pleasant IMHO.  I came across the following post by Jon Galloway that puts to use SQL Server 2005's XML capabilities to allow an XML fragment containing multiple values to be passed in as a single parameter and parsed.  Sure, there are pros and cons to this approach, but this technique makes reaching the end goal faster and easier than some of the alternatives.  Especially if you consider using the XML serialization capabilities in .NET to automatically serialize object properties to XML that is then passed to a sproc.

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

Published Friday, February 16, 2007 12:58 PM by dwahlin
Filed under: ,

Comments

# re: Passing an Array of Values to a Stored Procedure in SQL Server 2005

Friday, February 16, 2007 4:56 PM by Jon Galloway

I agree that there are pro's and con's to this. This still feels sloppy to me, just a little less sloppy than the alternatives.

# re: Passing an Array of Values to a Stored Procedure in SQL Server 2005

Friday, February 16, 2007 5:25 PM by dwahlin

I agree...way less sloppy than parsing commas or other separators. :-)  Until we can pass a "true" CLR object as a parameter I personally think this is a nice work-around to the issue though.  

I'm a big fan of XML serialization/deserialization and this plays nicely with it for both input and output parameters.  Multiple objects with multiple properties could be passed in as XML and then parsed and inserted, deleted or updated with a single sproc rather than making multiple calls like many apps seem to do.

# re: Passing an Array of Values to a Stored Procedure in SQL Server 2005

Monday, February 19, 2007 6:09 PM by likwid

Not saying it is better, but I don't understand why Xml is "way less sloppy."  Xml is pretty verbose, I tend to avoid it.

We wrote a function at work that returns a Table variable.  This way you can even do joins with it.  

Create   Function [dbo].[fn_ParseDelimitedStrings]

(@String nvarchar(3500), @Delimiter char(1))

Returns @Values Table

(

RowId int Not Null Identity(1,1) Primary Key

,Value nvarchar(255) Not Null

)

As

Begin

Declare @startPos smallint

,@endPos smallint

If (Right(@String, 1) != @Delimiter)

Set @String = @String + @Delimiter

       Set @startPos = 1

Set @endPos = CharIndex(@Delimiter, @String)

While @endPos > 0

       Begin

Insert @Values(Value)

Select LTrim(RTrim(SubString(@String, @startPos, @endPos - @startPos)))

-- remove the delimiter just used

Set @String = Stuff(@String, @endPos, 1, '')

-- move string pointer to next delimiter

Set @startPos = @endPos

                   Set @endPos = CharIndex(@Delimiter, @String)

End

Return

End

# re: Passing an Array of Values to a Stored Procedure in SQL Server 2005

Friday, February 23, 2007 3:22 AM by dwahlin

Thanks for posting the sample since it's a great example of alternative techniques.

That's an awful lot of string parsing code in my opinion although I'll agree that once you've written it once it can be re-used since it's in a function.  I like the ability to serialize .NET objects as XML and pass that in though and then easily parse it using the built-in XPath/XQuery functionality.  XML is certainly more verbose though as you mention.

# re: Passing an Array of Values to a Stored Procedure in SQL Server 2005

Thursday, May 17, 2007 7:47 PM by John Cleary

The XML serialization method sounds nice and ill probably use it myself but it ties the business layer into the data layer. If a variable name gets changed in the business layer then the stored procedure will also need to be updated to reflect this change.