|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Variable Number of Parameters
Hi all,
Can anyone tell me if its possible to pass in a variable number of parameters into a Stored Procedure. Thanks tce You could have "optional" parameters (if they have a default value, you can
left them blank). You may want to explain what you want to do so that you can get better advices (for example is this for an IN clause ? In this case they are generally "packed" into a single paramter and splitting by a function to be used as you can't have an arbitray number of parameters). Patrice -- Show quoteHide quote"thechaosengine" <none> a écrit dans le message de news:15489632512546710312500@news.microsoft.com... > Hi all, > > Can anyone tell me if its possible to pass in a variable number of parameters > into a Stored Procedure. > > Thanks > > tce > > Yes, assuming the optional parameters has default values:
CREATE PROC p @v1 int, @v2 int = 1 AS .... GO EXEC p @v1 = 25 or EXEC p @v1 = 25 , @v2 = DEFAULT -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "thechaosengine" <none> wrote in message news:15489632512546710312500@news.microsoft.com... > Hi all, > > Can anyone tell me if its possible to pass in a variable number of parameters into a Stored > Procedure. > > Thanks > > tce > > Certainly you can declare OPTIONAL parameters in a proc:
CREATE PROC dbo.usp_foo (@p1 INTEGER = NULL, @p2 INTEGER = NULL, @p3 INTEGER = NULL) AS SELECT @p1, @p2, @p3 GO EXEC dbo.usp_foo 1 EXEC dbo.usp_foo 1,2 EXEC dbo.usp_foo 1,2,3 You need to specify a default value for the optional parameters (NULL in this case). -- David Portas SQL Server MVP -- Can you do ParamArray in a CLR stored proc in Yukon??
- Sahil Malik [MVP] http://codebetter.com/blogs/sahil.malik/ Show quoteHide quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1115654823.179456.296450@z14g2000cwz.googlegroups.com... > Certainly you can declare OPTIONAL parameters in a proc: > > CREATE PROC dbo.usp_foo > (@p1 INTEGER = NULL, > @p2 INTEGER = NULL, > @p3 INTEGER = NULL) > AS > SELECT @p1, @p2, @p3 > GO > EXEC dbo.usp_foo 1 > EXEC dbo.usp_foo 1,2 > EXEC dbo.usp_foo 1,2,3 > > You need to specify a default value for the optional parameters (NULL > in this case). > > -- > David Portas > SQL Server MVP > -- > Hi Sahil,
as long as the is no Array type SQL that won´t work, but you can pass a string separated with a characters like (a,b,c,d) and split that to a string array and work with it in .NET (That´s quite that what a (string) array is useful for)). If you want more control over prefetchd data you can get them via the execution text in your .NET assembly. Working on some FAQs on my homepage, perhaps you check it sometimes to get some more information ;-) HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Show quoteHide quote "Sahil Malik [MVP]" <contactmethrumyblog@nospam.com> schrieb im Newsbeitrag news:%23o4H8FQVFHA.1508@tk2msftngp13.phx.gbl... > Can you do ParamArray in a CLR stored proc in Yukon?? > > - Sahil Malik [MVP] > http://codebetter.com/blogs/sahil.malik/ > > > "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message > news:1115654823.179456.296450@z14g2000cwz.googlegroups.com... >> Certainly you can declare OPTIONAL parameters in a proc: >> >> CREATE PROC dbo.usp_foo >> (@p1 INTEGER = NULL, >> @p2 INTEGER = NULL, >> @p3 INTEGER = NULL) >> AS >> SELECT @p1, @p2, @p3 >> GO >> EXEC dbo.usp_foo 1 >> EXEC dbo.usp_foo 1,2 >> EXEC dbo.usp_foo 1,2,3 >> >> You need to specify a default value for the optional parameters (NULL >> in this case). >> >> -- >> David Portas >> SQL Server MVP >> -- >> > > Thank you :-) .. I'll be checkin' your site.
- Sahil Malik [MVP] http://codebetter.com/blogs/sahil.malik/ Show quoteHide quote "Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote in message news:OxitbMSVFHA.3152@TK2MSFTNGP12.phx.gbl... > Hi Sahil, > > as long as the is no Array type SQL that won´t work, but you can pass a > string separated with a characters like (a,b,c,d) and split that to a > string array and work with it in .NET (That´s quite that what a (string) > array is useful for)). If you want more control over prefetchd data you > can get them via the execution text in your .NET assembly. > > Working on some FAQs on my homepage, perhaps you check it sometimes to get > some more information ;-) > > HTH, Jens Suessmeyer. > > > --- > http://www.sqlserver2005.de > --- > > "Sahil Malik [MVP]" <contactmethrumyblog@nospam.com> schrieb im > Newsbeitrag news:%23o4H8FQVFHA.1508@tk2msftngp13.phx.gbl... >> Can you do ParamArray in a CLR stored proc in Yukon?? >> >> - Sahil Malik [MVP] >> http://codebetter.com/blogs/sahil.malik/ >> >> >> "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message >> news:1115654823.179456.296450@z14g2000cwz.googlegroups.com... >>> Certainly you can declare OPTIONAL parameters in a proc: >>> >>> CREATE PROC dbo.usp_foo >>> (@p1 INTEGER = NULL, >>> @p2 INTEGER = NULL, >>> @p3 INTEGER = NULL) >>> AS >>> SELECT @p1, @p2, @p3 >>> GO >>> EXEC dbo.usp_foo 1 >>> EXEC dbo.usp_foo 1,2 >>> EXEC dbo.usp_foo 1,2,3 >>> >>> You need to specify a default value for the optional parameters (NULL >>> in this case). >>> >>> -- >>> David Portas >>> SQL Server MVP >>> -- >>> >> >> > > Yes and no.
While a SP can be defined with default values for none, some or all parameters, writing the code to execute the SP with a selected set of parameters is a little tricky. When calling a SP you only have to pass the parameters that have been defined without default values. In ADO, you can't simply define a parameter as "missing". If you add it to the Parameters collection, you have to provide a value. Passing NULL as a value is still passing a value and the default is over-ridden. To execute an SP with selected parameters, you have to rebuild the Parameters collection--choosing just those parameters to add to the collection that you want to include each time the SP is called. I think it would be interesting to have a "Disabled" property on the Parameter so you could define it but decide whether or not it's passed to the server at runtime. I'll be discussing (and illustrating) this technique at my workshop in Toronto on June 18th (DevTeach) hth -- Show quoteHide quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "thechaosengine" <none> wrote in message news:15489632512546710312500@news.microsoft.com... > Hi all, > > Can anyone tell me if its possible to pass in a variable number of > parameters into a Stored Procedure. > > Thanks > > tce > >  
Other interesting topics
how to know if a DEFAULT exists or not
Perfmon counters into SQL Server table backup problems locking an SQL Server DB with .NET Why use null values? how do I search for duplicate values in normalized data ? Query's in Query Analyzer sometimes extremely slow Q: full backup sqlservr.exe -g ??? Unicode BULK INSERT |
|||||||||||||||||||||||
