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.