Sql Stored Procedure With a Lot of Parameters

Question

I am using Sql Server 2008. My Stored Procedure accepts almost 150 parameters. Is there anything wrong with that performance-wise?

Answer 1

Regulars in the SQL Server newsgroups will be familiar with the many quarrels between Joe Celko and Tony Rogerson and one of them is on the very subject of whether it is a good idea to use a stored procedure with a large number of parameters.

Because the question is specifically about performance, here's Tony side of the argument:

Don't use CSV/XML - use 1,000 Parameters instead!

Tony Rogerson is a fellow Brit (US = limey) and the title is ironic (US = NULL).

Answer 2

You should definitely identify the reasons for all the parameters.

1. Are they values to be inserted or updated into a table? Then you may be better off using a table-valued parameter for those values.
2. Are they complex selection criteria? Then I suspect you should use an XML parameter to pass the selection criteria; otherwise you might re-evaluate the frequency with which the various criteria are actually used. You may find a simpler SP would meet your needs in 80% of the cases.

Answer 3

Maybe it is not a problem from a performance perspective. But from a maintenance perspective.

You could consider sending the data as a single xml parameter. For details see:

http://msdn.microsoft.com/en-us/library/dd788497.aspx

Answer 4

Nothing wrong performance wise but it smells as something that could be better done with dynamic SQL. Hard to tell without seeing the code.

Answer 5

when you are using SQL Server 2008 you can use the new Table parameter. If the parameters are the same, you can easily use the table parameter.

Here is link to the MSDN. Here is another link, a bit more explained

Enjoy.

Answer 6

the max is 1,024, but i think 150 is little over