How to create sproc with unlimited number of parameters?

We have this kind of thing in C#, and I know the sp_executesql (or something like that) does it too. How do you create a stored procedure that has certain known parameters, followed by any number of additional parameters?

For example, you may have one that's called in any of these ways:

exec my_sp @.ID, @.text, @.P1, @.P2

exec my_sp @.ID, @.text, @.P1, @.P2, @.P3

exec my_sp @.ID, @.text, @.P4, @.P39087, @.P2

I can't find a syntax in the docs that really accommodates this.

Thanks in advance for your help!

Is something like this what you are looking for? This defines a procedure with M required parameters and N optional parameters:

create procedure dbo.xample
( @.pm_required_01 <type#01>,
@.pm_required_02 <type#02>,
...
@.pm_required_M <type#M>,
@.pm_optional_01 <type#01> = <defaultValue#01>,
@.pm_optional_02 <type#02> = <defaultValue#02>,
...
@.pm_optional_N <Type#N> = <defaultValue#N>
)
as

yourCode

|||Does this solution enforce a maximum number of parameters though? If so, that's not quite what I was after.

I guess the other problem too is that there has to be some way to iterate through all of the parameters as well.|||

Jeff:

It sounds to me like you want to use a "list" as an argument to the stored procedure; if that is the case examine this article by Erland Sommarskog and see if this is the correct orientation:

http://www.sommarskog.se/arrays-in-sql.html

Also, there is a response here from Jens Suessmeyer that has some more about using a "string list":

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||

TSQL has a hard limit of ~2K Parameters to any module. Thus, there is no way to do what your asking natively in TSQL (i.e, the best you can do is 2K sql_variant parameters with a default defined ).

People have used various techniques to get around this problem, most of which are nicely summarized in the links above.