JCTDD
asked on
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(@Dat eStart 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
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(@Dat
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
Create this table-valued function that can split the comma-separated values passed to the stored procedure parameter
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]('OfficeN ame1,Offic eName2')
Raj
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
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]('OfficeN
Raj
ASKER
thanks Raj! I will have to test this and let you know the outcome
ASKER
Hi Raj I have created the function but not sure where to put the split below?
running SELECT * from [dbo].[TsqlSplit]('OfficeN ame1,Offic eName2') 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';
SELECT * from [dbo].[TsqlSplit]('OfficeName1,OfficeName2')
running SELECT * from [dbo].[TsqlSplit]('OfficeN
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';
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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](@RegionC ode))
"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](@RegionC
e.g
declare @RegionCode VarChar(50) =null;