Click here to Skip to main content
15,065,044 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
SQL
CREATE PROCEDURE sp_fuelmargin @storeid varchar(50),@fromdate datetime,@todate datetime

AS

select storeid,businessDate,fuelGrade,fuelGradeDesc,SUM(sales) as Sales,sum(Volume)as Volume from FuelSaleSummary where storeid in (@storeid ) and businessDate between @fromdate and @todate
group by StoreId,businessDate,fuelGrade,fuelGradeDesc
order by StoreId
GO

EXEC sp_fuelmargin 'tx001-strb,TX000-IFFI','2014-01-01','2014-05-30'


in the above storeprocedure contain storeid,fromdate,todate parameters i want to pass 2 store ids in to storeid parameter, when execute storeprocedure like above EXEC statement i am not getting data, please help me how to pass 2 ids into single parameter
Posted
Updated 24-Jul-14 23:44pm
v2
Comments
King Fisher 25-Jul-14 5:54am
   
Getting any Error?

Execute this Spit function in you DB:

SQL
create FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)


and Alter your Sp like this,
SQL
alter PROCEDURE sp_fuelmargin @storeid varchar(50),@fromdate datetime,@todate datetime
 
AS
 
select storeid,businessDate,fuelGrade,fuelGradeDesc,SUM(sales) as Sales,sum(Volume)as Volume from FuelSaleSummary where storeid in (select data from dbo.split(@storeid,',')) and businessDate between @fromdate and @todate
group by StoreId,businessDate,fuelGrade,fuelGradeDesc
order by StoreId
GO


now exec your Sp:
SQL
EXEC sp_fuelmargin 'tx001-strb,TX000-IFFI','2014-01-01','2014-05-30'
   
You can use table variable and pass the store id's values to it and then you can further use it.

Hope this helps you!! :)
   
You can only pass in as many parameters as you have declared in the sproc. To do what you want you will need to pass the list of StoreIds as a single string and parse it before you get to the SELECT
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900