Max Number of SP Input Parameters
-
I've done a little searching here, and found a possible answer, but i want to check again.
Background - I was asked to take one of those online skills test (I won't say which one to hopefully avoid copyright issues, blah blah, blah...). One of the questions was:
What is the maximum number of Input Parameters allowed for a stored procedure?
A) 64
B) 128
C) 256
D) 512
E) There is no limit to the number of input parameters.
I answered E, but that is not really a good answer. From my research, I have found that there is a max of 2100 total parameters (input + output) for SQL Server 2000 and 1024 total parameters for SQLServer 7. As far as I can tell, none of the answers to the question are correct.
Does anyone have a definitive answer? I found the 1024 answer in Ken Henderson's Guru's Guide to Transact-SQL, and the 2100 answer in BOL. No one addresses the max number of Input Parameters (if there is a distinction).
-
In my copy of books online (Sql server 2000, not perfectly up to date) I took a look at Contents | SQL Server Architecture | Implementation Details | Maximum Capacity Specifications | Parameters per stored procedure and I find the number 1024.
I'm curious. Where is 2100 specified in books on line?
Wayne
-
I have the Updated - SP3 version of BOL.
Under Index --> limitations-SQL Server objects -->Parameters per stored procedure it lists 1024 for SQL Server 7 and 2100 for SQL Server 2000. I found it somewhere else as well, but I can't put my finger on it right now.
-
Aha, found it:
Index --> Create Procedure:
n
Is a placeholder indicating that a maximum of 2,100 parameters can be specified.
-
And wouldn't that be a fun one to debug?
There is no "i" in team, but idiot has two. -
I've never used more than 10 parameters in my progs (almost all queries are done by access in adps).. I can't imagine using more than 25-100 parameters in a sp.
-
Ya just gotta love that Microsoft documentation... the most number of parameters passed to a proc that I've ever seen was 120 and that was a C.R.U.D. proc for a really stupid GUI. About a year ago, I saw someone ask how to replicate a 600 column table (can't be done, too many columns for replication not to metntion probably violates 3rd normal form)... imagine writing C.R.U.D. for that bad boy!
I'm with Remi, usually it's less than 4 or 5 parameters and almost always less than 10 (unless it's C.R.U.D.)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
When you look at "Maximum capacity specifications" in BOL you'll also find a row telling
Maximum sizes/numbers Object SQL Server 7.0 SQL Server 2000 ...
Parameters per stored procedure 1,024 2,100 Personally I think, if you get here anyway near that limit, you have definitely other problems.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It probabely violates all the normal forms...
-
Yep, that's why I was confused. BOL states 1024 (SQL 7) or 2100 (SQL 2000). My closest choices were 512 and no limit (and the question was about input parameters, so it was wrong on several levels).
I just hope that my guess (no limit) was the "correct" wrong answer.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply