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:
|||Does this solution enforce a maximum number of parameters though? If so, that's not quite what I was after.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>
)
asyourCode
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.
No comments:
Post a Comment