SQL Max parameters

Question

I read here that the maximum number of parameters that can be passed to a Stored Procedure is 2100.

I am just curious what kind of system would require a SP with 2100 parameters to be passed, and couldn't one split that into multiple SPs?

I thought that maybe an SP that calls multiple SPs would require a lot of params to be passed, I just can't fathom writing out that disgusting EXEC statment.

Answer 1

The limit of procedures parameters predates both the XML data type and the table valued parameters, so back in those days there was simply no alternative. Having 2100 parameters on a procedure does not necessarily mean a human wrote it nor that a human will call it. It is quite common in generated code, like code created by tools and frameworks, to push such boundaries for any language, since the maintenance and refactoring of the generated code occur in the generating tool, not in the result code.

Answer 2

If you have a stored procedure using 2100 parameters you most likely have some sort of design problem.

Passing in a CSV list of values in a single parameter (and using a table value split function to turn those into rows), or using a table value parameter would be much easier than handling all of those input parameters.

Answer 3

I had a situation where I had to run something quite like the following:

SELECT 
  ...
WHERE
  ID IN (?,?,?,?...)

The list of parameters featured all entities the user had permission to use in the system (it was dynamically generated by some underlying framework). Turns out that the SGBD had a limitation on the number of parameters to be passed like this, and it was below 2100 (IIRC, it was Oracle and the maximum were 999 parameters in the IN list).

This would be a nice example of a rather long list of parameters to something that had to be a stored procedure (we had more than 999 and less than 2100 parameters to pass).

Don't know if the 999 constraint apply to sql server, but it is definately a situation where the long list would be useful...