如何将多个参数传递给mysql存储过程

I have a stored procedure that I am working on that is returning an invalid return when I try to pass parameters to it compared to when I run it with hard-coded values.

我有一个我正在处理的存储过程,当我尝试将参数传递给它时返回无效返回与使用硬编码值运行它时相比。

Procedure with hard coded values:

具有硬编码值的过程:

BEGIN
    SELECT COUNT(DISTINCT ItemID) 
    FROM ( 
        SELECT * 
        FROM sandbox_inventoryitempurchase 
        WHERE OrgID = '2781823' 
            AND PurchaseMonth>'2015-03-01'
    ) as DistinctCount;
END

When run, this returns: 16 which is correct.

运行时,返回:16这是正确的。

Procedure with two input parameters:

具有两个输入参数的过程:

BEGIN
    SELECT COUNT(DISTINCT ItemID) 
    FROM ( 
        SELECT * 
        FROM sandbox_inventoryitempurchase 
        WHERE OrgID = orgid 
            AND PurchaseMonth>sincedate
    ) as DistinctCount;
END

The input parameters are defined as:

输入参数定义为:

IN userid integer,IN orgid integer,IN sincedate date

IN用户ID整数,IN orgid整数,IN sincedate日期

When run, this returns: 334 which is not correct.

运行时,返回:334这是不正确的。

I am new to stored procedures and would appreciate any assistance offered regarding what I am doing wrong and what I need to do to resolve?

我是存储过程的新手,非常感谢我提供的任何帮助,说明我做错了什么以及我需要做些什么才能解决?

Thanks...

3 个解决方案

#1

add "@" before your parameters:

在参数前添加“@”:

BEGIN 
    SELECT COUNT(DISTINCT ItemID) 
    FROM ( 
       SELECT * 
       FROM sandbox_inventoryitempurchase 
       WHERE OrgID = @orgid 
           AND PurchaseMonth>@sincedate
    ) as DistinctCount; 
END

a fast turtorial will be: Write-a-Stored-Procedure-in-SQL (see how to use parameters at the end of this page...)

一个快速的turtorial将是:Write-a-Stored-Procedure-in-SQL(请参阅本页末尾的如何使用参数...)

#2

Try using input parameters that are not the same name as your columns. Change:

尝试使用与列名称不同的输入参数。更改:

BEGIN
    SELECT COUNT(DISTINCT ItemID) 
    FROM ( 
        SELECT * 
        FROM sandbox_inventoryitempurchase 
        WHERE OrgID = orgid 
            AND PurchaseMonth>sincedate
    ) as DistinctCount;
END

To:

BEGIN
    SELECT COUNT(DISTINCT ItemID) 
    FROM ( 
        SELECT * 
        FROM sandbox_inventoryitempurchase 
        WHERE OrgID = p_orgid 
            AND PurchaseMonth > p_sincedate
    ) as DistinctCount;
END

And pass IN p_orgid integer,IN p_sincedate date

并传递IN p_orgid整数,IN p_sincedate日期

The parser may be seeing WHERE OrgID = orgid and that evaluates to true for every record since it evaluates as an identity (comparing against itself).

解析器可能正在看到WHERE OrgID = orgid,并且每个记录的评估结果为true,因为它评估为身份(与自身进行比较)。

#3

Param Name and field name must be different!

参数名称和字段名称必须不同!

BEGIN SELECT COUNT(DISTINCT ItemID) FROM ( SELECT * FROM sandbox_inventoryitempurchase WHERE OrgID = p_orgid AND PurchaseMonth > p_sincedate ) as DistinctCount; END

BEGIN SELECT COUNT(DISTINCT ItemID)FROM(SELECT * FROM sandbox_inventoryitempurchase WHERE OrgID = p_orgid AND PurchaseMonth> p_sincedate)as DistinctCount;结束

#1