Passing many parameters to a stored procedure

Question

Passing many parameters to a stored procedure

teck-1054678

SSC Veteran

Points: 216
More actions
May 25, 2010 at 7:58 am

#141065

Hi.
What is the most efficient way of sending/passing parameters to a stored procedure from ADO.NET ?
Should I concatenate all the fields into a pipe delimited text stream and unpack in the procedure ?
Is there a way to pass xml into a procedure and unpack that ?
Thanks...

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

If you pass XML, then you'll need to shred it just like if you pass delimited data that needs to be parsed. A lot of folks use XML for such a thing but I generally prefer just delimited data. Delimited data is a little easier on the the network because it doesn't have all the tag overhead to pass.

With that in mind, please see the following article...

http://www.sqlservercentral.com/articles/T-SQL/63003/

Pardon the "error" in the article... somewhere along the line, I forgot to flag things as the introduction would lead you to believe but it doesn't affect the content of the article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 2

I tend to use a delimited list if I have a predefined, repeating structure when calling the sproc.

But if I get something like "3 variable columns out of 20" I prefer using XML since it's easier to extract the name and the corresponding value.

So, I'd say "it depends". 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

Heh... yep... depends on whether you want to be nice to the "pipe" or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 4

Jeff Moden (5/25/2010)
Heh... yep... depends on whether you want to be nice to the "pipe" or not.

I have to admit that I try to balance "pipe load", overall performance and the effort I need to take to make it work. Sometimes the latter takes precedence as long as the middle one is acceptable not leaving enough room for the former 😀

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

teck-1054678 SSC Veteran Points: 216 More actions · Answer 5

But would I be right in saying that:

* For SS 2000 there is no easy way to unpack xml or perhaps OpenXml ?

* For SS 2005/2008 there is XQuery to unpack xml ??

teck-1054678 SSC Veteran Points: 216 More actions · Answer 6

Well, if I accept the overhead of passing xml, the advantage to me any way seems to be the procedure is easier to debug and read because the data items are tagged. If I just pass a pipe-delimited text stream then it does make testing and debugging more difficult, particularly where I may be passing 30+ parameters for a large insert/update.

I had a play with openXML with SS 2000 and on the face of it seems a reasonable solution:::

if exists(select name from sysobjects where name = 'TestOpenXML')

drop procedure dbo.TestOpenXML

go

create procedure dbo.TestOpenXML

(

@i_strXML varchar(2000)

)

as

declare @t_XMLDocPointer int

exec sp_xml_preparedocument @t_XMLDocPointer output, @i_strXML

declare @t_name varchar(30)

declare @t_town varchar(50)

declare @t_county varchar(50)

declare @t_postcode varchar(12)

declare @t_age int

select @t_name = name,

@t_town = town,

@t_county = county,

@t_postcode = postcode,

@t_age = age

from openxml(@t_XMLDocPointer,'/root',2)

with ( name varchar(30),

town varchar(50),

county varchar(50),

postcode varchar(12),

age int )

if @@error <> 0

begin

raiserror('Error occured while using OPENXML', 16, 1)

return

end

select @t_name, @t_town, @t_county, @t_postcode, @t_age

exec sp_xml_removedocument @t_XMLDocPointer[/color]

-- EXECUTE THE PROCEDURE..

declare @i_strXML VARCHAR(2000)

set @i_strXML=

'<root>

<name>Fred Smith</name>

<town>Swindon</town>

<county>Wiltshire</county>

</root>'

exec dbo.TestOpenXML @i_strXML

Works fine...;-)