• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

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?

0
exceter
Asked:
exceter
2 Solutions
 
Rajkumar GsSoftware EngineerCommented:
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
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
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) 
                 )
)

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now