Solved

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

Posted on 2011-02-24
4
341 Views
Last Modified: 2012-05-11
Hi,
In programming languages you can pass array parameter to function or procedure.
Is it possible to pass array parameter to SQL procedure?

0
Comment
Question by:exceter
4 Comments
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 100 total points
ID: 34977288
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
 
LVL 4

Accepted Solution

by:
ong-hh earned 150 total points
ID: 34977436
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 34977476
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
 
LVL 10

Expert Comment

by:John Claes
ID: 34977567
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now