?
Solved

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

Posted on 2011-02-24
4
Medium Priority
?
358 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 400 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 600 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.
Suggested Courses

777 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