Solved

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

Posted on 2011-02-24
4
340 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

11 Experts available now in Live!

Get 1:1 Help Now