Long parameter list for stored procs

Hi All,

I've got a problem with increasingly long parameter lists for SProcs... Especially when one calls another SProc, and so on. Is there any way around this? Like can you dynamically construct a string and pass that? I'm just looking to see if more experienced players have found ways around this, or have just dealt with it by using well formed code.

Thanks

Chris

Have a look at the following article by Erland Sommarskog:
http://www.sommarskog.se/arrays-in-sql.html|||

thats a useful link carlop has given...

u can construct any synamic sql statement dynamically..using string and variables...and execute it..including calling an sp..

look for execute and sp_executesql in books online..

|||

Hi,

It is not bad idea to have more number of parameters. But i know it is more overhead when you call it.

If you want to reduce this you can go for XML string as Parameter & OPENXML to extract the XML data in your sp.

Basically XML Parameter is used to solve the following issue,

1. Multiple Row Insert on Single Call

2. Passing multiple data into single Parameter (Like Array of values)

Here I gave simple example with 2 Parameter A &B

XML String (Parameter):

<Parameters>

<Param name='A' value='10'/>

<Param name='B' value='15'/>

</Parameters>

OPENXML on your SP

Declare @.SpParams Table (Name varchar(100), Value Varchar(100));

DECLARE @.hDoc int
Exec sp_xml_preparedocument @.hDoc OUTPUT,@.XmlString

INSERT INTO @.SpParams
SELECT *
FROM OPENXML (@.hDoc,'/Parameters/Param')
WITH (Name varchar(100), Value Varchar(100))

EXEC sp_xml_removedocument @.hDoc;

Now you can use the @.spparams table on nessasary place....

BUT,

If you ask me, it is best practice for Mulitple row insert and passing array of value.

For your concern it always better use as indivdual params. (see the complex/overhead code on sp to prepare the document, parse the XML, and read the content on every query)

|||To me it is a question of numbers. If you have a long list of parameters, but they are fixed in number, then I would certainly stick to just writing well formed queries, not putting stuff into arrays or any of that other stuff. That method should be considered mostly for unknown numbers of parameters, paticularly when you need to pass unknown numbers of values to the same parameters. The more clear to follow that your procedures are (regardless of size) the easier that "future you" will remember what the heck you had in mind when you wrote the procedure. If you are poking around in a string value to parse out different parameter values, your chances of making a "dumb" mistake later that will not be caught until the president of your company is trying to demo the program for a potential client.