Crazy number of Parameters… and a challenge

Comments 1

Share to social media

The other day, my lovable coworker and frequent Simple Talk writer: Grant Fritchey, sent this post on X:

So I replied: “Challenge Accepted”, well, actually I replied with a gif of Neo saying it, but the effect was the same. So, I decided to just see, what would that look like. Coincidentally I am testing the new template for the Simple Talk site, and a function with 2100 parameters seemed like some code that screams out: “BIG!” Testing is should always be about pressing the limits of your code, so why not.

NOTE: The point of this is NOT to suggest that you ought to do something like this, it is to show you just how large and unwieldy a procedure with 2100 parameters actually is! It is actually so much worse than I expected, because with 1 parameter per line, and then 1 parameter per line in the code; it turned out to be a single spaced, 73 page document in word.

The craziest code I have ever compiled

So, I decided on just a simple procedure that had 2100 integer parameters. I used the following two bits of code to create the parameters, named @param1, @param2, over and over:

These generated 2100 parameters like: @Param1 int = 1 and then @Param1 +. The code basically adds 2100 numbers. At least that code is simple. The procedure looks something like this:

But looking at that, no biggie right? Well in the final section of this article, you can see the entire code!

The program does in fact work. You can compile the code and execute:

And you will see that this returns:

If you try adding a 2101st parameter will give you the following error (I got this by added a first parameter @param0 int = 0,):

Msg 180, Level 15, State 1, Procedure MaxParameters, Line 2102 [Batch Start Line 12]
There are too many parameters in this CREATE PROCEDURE statement. The maximum number is 2100.

Msg 137, Level 15, State 2, Procedure MaxParameters, Line 4204 [Batch Start Line 12]
Must declare the scalar variable "@param2100".

The Challenge

An alternative if you needed 2101 parameters, could be to… actually, no. I was considering building another example that used a table variable, but then it hit me… this is all kind of madness. The question really is, what is the maximum number of parameters that would be a reasonable?

That is the challenge.

As you will see in the final section, this is WAY too many parameters to be all that valuable. I know there is some use case that led to that number (otherwise it would likely be either a round number (like 2000), or a binary type value of 2048 or something similar.

The challenge is… Convince me of the largest number of parameters that you REALLY need. The more realistic example that has the largest number of parameters the better. I

I don’t really even have an example in my mind from where I needed more parameters than the number of columns a table might have, plus a few parameters to set some status flag or something. So no more than 200 when I wasn’t just trying things.

Appendix: The Craziest Code I have Ever Compiled

Here’s the code, note that there are scroll bars so you can see every line of the code!

Article tags

About the author

Louis Davidson

Simple Talk Editor

See Profile

Louis is the editor of this Simple-Talk website. Prior to that, has was a corporate database developer and data architect for a non-profit organization for 25 years! Louis has been a Microsoft MVP since 2004, and is the author of a series of SQL Server Database Design books, most recently Pro SQL Server Relational Database Design and Implementation.