Variable Number of Parameters

  • Thread starter Thread starter thechaosengine
  • Start date Start date
T

thechaosengine

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
 
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
 
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).
 
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.
 
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

--
____________________________________
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.
__________________________________
 
Back
Top