exceter
asked on
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?
In programming languages you can pass array parameter to function or procedure.
Is it possible to pass array parameter to SQL procedure?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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)
)
)
Bye, Olaf.