作者:萝莉控的小潴_515 | 来源:互联网 | 2022-12-21 15:45
我是新来的。有一个奇怪的问题。我的T-SQL查询返回69行,但是存储过程中的同一查询返回零结果。
在StackOverflow的其中一篇文章中,有人切换了EXEC语句中的参数/变量,但这对我也不起作用,如下所示。
除了BETWEEN
,我还尝试了>=
和<=
,但没有运气。
谢谢。
SELECT
T1.CustKey
FROM
INVOICEROWS T0
INNER JOIN
INVOICE T1 ON T1.INVKEY = T0.INVKEY
INNER JOIN
ITEMS T2 ON T2.PARTCODE = T0.PARTCODE
INNER JOIN
CUSTOMERS T5 ON T5.CustKey = T1.CustKey
WHERE
T1.INVDATE BETWEEN '2018-03-26' AND '2018-03-26'
AND ((T1.CustKey BETWEEN 'ABC' AND 'ABC') OR (T5.CustGroupKey = ''))
AND ((T0.ItemKey BETWEEN 'PQR' AND 'STU') OR (T2.ItemGroupKey = ''))
结果:69行具有相同的值“ ABC”(正确)
ALTER PROCEDURE [dbo].[PROC1]
(@DATEFROM AS DATE,
@DATETO AS DATE,
@CUSTKEYFROM AS NVARCHAR,
@CUSTKEYTO AS NVARCHAR,
@CUSTGROUPKEY AS SMALLINT,
@ItemKeyFrom NVARCHAR(20),
@ItemKeyTo NVARCHAR(20),
@ItemGroupKey NVARCHAR(11)
)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
T1.CustKey
FROM
INVOICEROWS T0
INNER JOIN
INVOICE T1 ON T1.INVKEY = T0.INVKEY
INNER JOIN
ITEMS T2 ON T2.PARTCODE = T0.PARTCODE
INNER JOIN
CUSTOMERS T5 ON T5.CustKey = T1.CustKey
WHERE
T1.INVDATE BETWEEN @DATEFROM AND @DATETO
AND ((T1.CustKey BETWEEN @CUSTKEYFROM AND @CUSTKEYTO) OR (T5.CustGroupKey = @CUSTGROUPKEY))
AND ((T0.ItemKey BETWEEN @ItemKeyFrom AND @ItemKeyTo) OR (T2.ItemGroupKey = @ItemGroupKey))
END
运行此存储过程:
EXEC PROC1 '2018-03-26', '2018-03-26', 'ABC', 'ABC', '', 'PQR', 'STU', ''
返回零行
EXEC PROC1 '2018-03-26', '2018-03-26', 'ABC', 'PQR', '', 'ABC', 'STU', ''
也返回零行
1> marc_s..:
我敢打赌,您的问题是两个存储过程参数的声明错误:
ALTER PROCEDURE [dbo].[PROC1]
(@DATEFROM AS DATE,
@DATETO AS DATE,
@CUSTKEYFROM AS NVARCHAR,
@CUSTKEYTO AS NVARCHAR,
@CUSTGROUPKEY AS SMALLINT,
@ItemKeyFrom NVARCHAR(20),
@ItemKeyTo NVARCHAR(20),
@ItemGroupKey NVARCHAR(11)
)
你拥有@CUSTKEYFROM
并@CUSTKEYTO
宣布为正义NVARCHAR
。这意味着:您得到的字符串长度恰好是一个字符!这很少是您期望或想要的。
尝试这两个参数声明,长度 -你应该总是做的!-我敢打赌,您的存储过程将返回所需的结果!
ALTER PROCEDURE [dbo].[PROC1]
(@DATEFROM AS DATE,
@DATETO AS DATE,
-- add explicit LENGTH to the NVARCHAR! However long you need it
@CUSTKEYFROM AS NVARCHAR(25),
@CUSTKEYTO AS NVARCHAR(25),
请参阅不良习惯:立即宣告VARCHAR不带(长度),以更好地,全面地解释您应该真正善待的“不良习惯”