How many stored procedure parameters is too many?

Question

I've just started writing a stored procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too many parameters?

For context... this procedure will essentially INSERT a single row into a single table. There would also be a very similar; albeit somewhat smaller; version that performs an UPDATE on the same table. Most columns are relatively small with a mix of int and strings (varchar(200)).

What are the issues; good or bad; to having a procedure with a large number of parameters and what is the threshold where I should start considering other patterns?

Answer 1

Issues? I'd argue none.

• The limit is 2100 parameters. IIRC it has been 2100 since SQL2000 but a documentation error suggested it was 1024.
• If a table has 1000 columns (due to a Sharepoint-esque sparse column arrangement for example) and you are enforcing access via stored procedures, your insert proc may have 1000 parameters. Nothing wrong with that.
• Do pause to review the schema when you encounter a wide table (not that 30 is particularly wide). It's not uncommon to find tables that started off life normalized but through laziness and/or fecklessness have expanded beyond recognition.
• Don't even briefly consider passing a set of parameters as a CSV list or XML. Blinds the query optimiser and saves little to no time or effort.
• Don't hand crank the client code to call a procedure with a large number of parameters. Code generation tools like T4 templates or CodeSmith to the rescue.

Answer 2

Joe Celko is an advocate of long parameter lists, which he writes about in detail in this two-part article:

The simplest answer is to use a long parameter list to construct lists and derived tables inside the procedure body. SQL server can handle up to 2100 parameters, which should be more than enough for practical purposes. SQL Server is actually a wimp in this regard; DB2 ;can pass 32K parameters. and Oracle can have 64K parameters.

...the long parameter list is made up of plain old parameters, which means that they can be used for outputs as well as inputs. It also is contained in one statement for the optimizer to use.

Do I think these techniques will always be the best solution? Of course not. There is no such thing in SQL. But it is worth looking at when you get an appropriate problem.