Is it possible to pass not fixed number of parameter to SQL Procedure ?

Hi,
In programming languages you can pass array parameter to function or procedure.
Is it possible to pass array parameter to SQL procedure?

LVL 3

exceterAsked: 2011-02-24

Who is Participating?

LVL 4

ong-hhCommented: 2011-02-24

you can use xml

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

LVL 23

Rajkumar GsSoftware EngineerCommented: 2011-02-24

Either you can pass comma separated values to a single string parameter in SQL Server Stored Procedure. Inside Stored Procedure, you need to split it off

CREATE PROCEDURE [dbo].[YourStoredProcedure] 
(
	@YourValue varchar(4000) -- Sample Input data '100,32,4,22,'
)
AS
BEGIN.......

Open in new window

Or

Specify some parameters and set default to some value. For those parameters with default values, it is optional.

CREATE PROCEDURE [dbo].[usp_YourStoredProcedure] 
(
	@StrSalesPersonID varchar(500),
	@StartDate DateTime =null,
	@EndDate DateTime=null
)
AS
.....

Open in new window

In this sample Stored Procedure, only @StrSalesPersonID is mandatory, other parameters are not, since they are default to null

LVL 31

Olaf DoschkeSoftware DeveloperCommented: 2011-02-24

Definein @parameter = defaultvalue makes the parameter optional to the caller. The parameter not passed in then has the default value. You can eg set the default to NULL and then use ISNULL() or COALESCE() or IS NULL in the procedure to make a difference, if the parameter is really passed in or not.

Bye, Olaf.

LVL 10

John ClaesSenior .Net Consultant & Technical AnalistCommented: 2011-02-24

using defaulted Parameters you should be aware of the maximum of the non-fixed lentgh of parameters Fields.

Xml and the split (Xml is my favorit) can be used for multiple values for 1 field

So you should combine the previous answers together

Make for every ParameterField an defaulted paramete
** For every Field that Could be send you make a defaulted parameter

Every Parameter then must be of type Xml (or nvarchar(XXXX))
and can be checked like used in :
http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

So my proposal would be something like

CREATE PROCEDURE [dbo].[usp_YourStoredProcedure] 
(
	@StrSalesPersonID int,
	@StartDate xml =null,
	@EndDate xml=null
)
AS
..... 
select *
from YourTable
where 
StrSalesPersonID = @StrSalesPersonID AND
(
    @EndDate is null OR
    EndDate in (   SELECT ParamValues.EndDate.value('.','DATETIME')
                   FROM @EndDate.nodes('/EndDates/EndDate') 
                   as ParamValues(EndDate) 
               )
) AND 
(
    @StartDate is null OR
    StartDate in ( SELECT ParamValues.StartDate.value('.','DATETIME')
                   FROM @StartDate .nodes('/StartDates/StartDate) 
                   as ParamValues(StartDate) 
                 )
)