如何在具有多个参数的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'

上面的storeprocedure中的
包含storeid，fromdate，todate参数我想通过2将ids存储到storeid参数，当执行storeprocedure如上所述EXEC语句我没有得到数据时，请帮我如何将2个ID传递给单个参数

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

推荐答案

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
)

并像这样改变你的Sp，

and Alter your Sp like this,

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

现在执行你的Sp：

now exec your Sp:

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

之前解析它

这篇关于如何在具有多个参数的sql中执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！