SQL Stored Procedure - have the option to enter 1 parameter or many

using SQL server 2000

stored procedure name is dbo.spCRAIA
this sp has 3 parameters
i) dateStart - 1 value
ii) dateEnd - 1 value
iii) regionCode - 1 or many value

there are more than 20 regionCodes sometimes will need to input 1 sometimes might need to input 2 or more how to achieve this?

there are 2 ways I can execute this SP

first way to execute sp
CREATE Procedure dbo.spCaptureRateARIA(@DateStart Datetime, @DateEnd Datetime, @RegionCode VarChar(50))

exec dbo.spCRAIA '01 july 2010', '31 january 2011', 'OfficeName';


second way to execute sp
declare @DateStart Datetime
declare @DateEnd Datetime
declare @RegionCode VarChar(50)

set @DateStart = '01 july 2010'
set @DateEnd = '31 january 2011'
set @RegionCode = 'OfficeName1'

drop table #a

select sum(chequeamount) as ROInc_amt, 0 as SSFSInc_amt
into #a
from payregion_vw
where paymentdate >= @DateStart and paymentdate <= @DateEnd
and regioncode in (@RegionCode)
union
select 0 as ROInc_amt, sum(chequeamount) as SSFSInc_Amt
from payregion_vw
where paymentdate >= @DateStart and paymentdate <= @DateEnd
and regioncode in (@RegionCode)

-- get rate

select @RegionCode as 'Rate',
sum(Inc_amt)/ sum(ROInc_amt)*100 as 'Inc%',
sum(ROInc_amt)/1000000 as 'AmtROInc'
from #a
JCTDDAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Just modify the WHERE condition from
...and regioncode in (@RegionCode)

Open in new window

to
...and regioncode in (SELECT Item FROM [dbo].[TsqlSplit](@RegionCode))

Open in new window


Raj
0
 
lalitgadaCommented:
Default set to null is that is optional
e.g
declare @RegionCode VarChar(50) =null;
0
 
Rajkumar GsSoftware EngineerCommented:
Create this table-valued function that can split the comma-separated values passed to the stored procedure parameter
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go


CREATE FUNCTION [dbo].[TsqlSplit] ( @List AS VARCHAR(8000) )
RETURNS @Items TABLE
    (
      Item VARCHAR(8000) NOT NULL
    )
AS 
    BEGIN
        DECLARE @Item AS VARCHAR(8000) ,
            @Pos AS INT
        WHILE DATALENGTH(@List) > 0 
            BEGIN
                SET @Pos = CHARINDEX(',', @List)
                IF @Pos = 0 
                    SET @Pos = DATALENGTH(@List) + 1
                SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
                IF @Item <> '' 
                    INSERT  INTO @Items
                            SELECT  @Item
                SET @List = SUBSTRING(@List, @Pos + DATALENGTH(','), 8000)
            END
        RETURN
    END

Open in new window


Now you can pass the values for @RegionCode  as 'OfficeName1,OfficeName2'
comma-separated and wrapped by single-quotes as an entire parameter

Inside your stored procedure, you can split it like
SELECT * from [dbo].[TsqlSplit]('OfficeName1,OfficeName2')

Raj


0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
JCTDDAuthor Commented:
thanks Raj! I will have to test this and let you know the outcome
0
 
JCTDDAuthor Commented:
Hi Raj I have created the function but not sure where to put the split below?
SELECT * from [dbo].[TsqlSplit]('OfficeName1,OfficeName2')

Open in new window


running SELECT * from [dbo].[TsqlSplit]('OfficeName1,OfficeName2') will produce:
ITEM
OfficeName1
OfficeName2

but how to use these in the parameter @RegionCode?

this works: exec dbo.spCRAIA '01 july 2010', '31 january 2011', 'OfficeName';

what is the syntax or steps to execute the SP with more than one office e.g.
exec dbo.spCRAIA '01 july 2010', '31 january 2011', 'OfficeName1, OfficeName2';


0
 
JCTDDAuthor Commented:
thanks Raj I get this error now:

"Cannot resolve collation conflict for equal to operation."

this error happens after I modified the where condition to:
and regioncode in (SELECT Item FROM [dbo].[TsqlSplit](@RegionCode))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.