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
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;结束