查询结果与存储过程中的查询不同
问题描述:
我做了一个查询来填充我的表并将其转换为存储过程。查询结果与存储过程中的查询不同
CREATE PROCEDURE [dbo].[sp_getSAPCMTX]
@sSDate varchar(10),
@sEDate varchar(10),
@sBranch varchar(4000)
AS
DELETE FROM SAPCMTX
WHERE DocDate BETWEEN @sSDate AND @sEDate AND
StoreCode IN (SELECT Item FROM dbo.SplitStrings(@sBranch , ','))
INSERT INTO SAPCMTX
SELECT * FROM Table T0
WHERE T0.DocDate BETWEEN @sSDate AND @sEDate AND T0.DataSource<>'O' AND
T1.TreeType<>'I' AND T0.DocType = 'I' AND
StoreCode IN COLLATE DATABASE_DEFAULT IN (SELECT Item FROM
dbo.SplitStrings(@sBranch , ','))
GO
要运行它我用这个命令
EXEC [dbo].[sp_getSAPCMTX]
@sSDate = '3/8/2016',
@sEDate = '3/8/2016',
@sBranch = '002'
它返回33行,但,当我尝试作为TSQL单独运行它返回34行。
DECLARE @sSDate varchar(10)
DECLARE @sEDate varchar(10)
DECLARE @sBranch varchar(4000)
SET @sSDate = '3/8/2016'
SET @sEDate = '3/8/2016'
SET @sBranch = '002'
DELETE FROM SAPCMTX
WHERE DocDate BETWEEN @sSDate AND @sEDate AND
StoreCode IN (SELECT Item FROM dbo.SplitStrings(@sBranch , ','))
INSERT INTO SAPCMTX
SELECT * FROM Table T0
WHERE T0.DocDate BETWEEN @sSDate AND @sEDate AND T0.DataSource<>'O' AND
T1.TreeType<>'I' AND T0.DocType = 'I' AND
StoreCode IN COLLATE DATABASE_DEFAULT IN (SELECT Item FROM
dbo.SplitStrings(@sBranch , ','))
GO
任何想法为什么结果有差异,考虑到它基本上是相同的查询?
答
这里有两种可能性,可以帮助你得到所期望的结果:
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [dbo].[sp_getSAPCMTX]
-- your code of the procedure
创建本地属性和复制的值:
-
SET ANSI_NULLS ON
改变过程之前参数:ALTER PROCEDURE [dbo].[sp_getSAPCMTX] @sSDate VARCHAR(10) @sEDate VARCHAR(10) @sBranch VARCHAR(4000) AS DECLARE @sDate VARCHAR(10) DECLARE @eDate VARCHAR(10) DECLARE @branch VARCHAR(4000) SET @sDate = @sSDate SET @eDate = @sEDate SET @branch = @sBranch -- your code of the procedure
+0
我试了第一个仍然是相同的 对于第二个,这就是我所做的只是为了得到所需的结果,但我仍然想知道为什么我需要创建局部变量以获得正确的输出 –
好吧 - 看看你回来的额外的行 - 然后你可以更容易地调试为什么和什么。 –