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

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.

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

Select all Open in new window