How many stored procedure parameters is too many?

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?

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.

StackExchange Database Administrators Q#11980, answer score: 14