如何在具有多个参数的sql中执行存储过程 [英] how to execute stored procedure in sql with multiple parameters

问题描述

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

推荐答案

执行这个Spit函数在你DB中：

Execute this Spit function in you DB:

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'

您可以使用表变量并将商店ID的值传递给它，然后您可以进一步使用它。



希望这可以帮到你!! ：）

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!! :)

您只能传入您在sproc中声明的参数。要执行您想要的操作，您需要将StoreIds列表作为单个字符串传递并在进入SELECT

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

之前解析它

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