时间戳

首页 > 解决方案 > 动态 TSQL 中的引号错误

tsql - 动态 TSQL 中的引号错误

问题描述

我尝试了很多帖子来解决下面的错误，但我被卡住了。如果有人可以在这里指导我正确的方向，将不胜感激。

-- Without EXEC -- this works fine
DECLARE @BPPA AS nvarchar(5)
SET @BPPA = 'BPP2'
DECLARE @BPPB AS nvarchar(5)
SET @BPPB = 'BPP4'

SELECT T0.CustCode AS 'CustCode', MAX(T0.CustName) AS 'CustName', T0.ItemCode AS 'ItemCode', MAX(T0.ItemName) AS 'ItemName', MAX(T0.BPP) AS 'BPP',
SUM(T0.Qty) AS 'QtyP1', SUM(T0.SalesAmt) AS 'SalesAmtP1', 0 AS 'QtyP2', 0 AS 'SalesAmtP2'
FROM [dbo].[VWAJ_SALANALYSIS] T0
WHERE T0.DocDate BETWEEN '2016-01-01' AND '2016-03-31'
AND (
((',' + RTRIM(T0.BPP) + ',') LIKE '%,' + @BPPA + ',%') OR ((',' + RTRIM(T0.BPP) + ',') LIKE '%,' + @BPPB + ',%')
)
GROUP BY T0.CustCode, T0.ItemCode

    -- With EXEC -- Error 'Must declare the scalar variable "@BPPA"
DECLARE @BPPA AS nvarchar(5)
SET @BPPA = 'BPP2'
DECLARE @BPPB AS nvarchar(5)
SET @BPPB = 'BPP4'

EXEC('
SELECT T0.CustCode AS ''CustCode'', MAX(T0.CustName) AS ''CustName'', T0.ItemCode AS ''ItemCode'', MAX(T0.ItemName) AS ''ItemName'', MAX(T0.BPP) AS ''BPP'',
SUM(T0.Qty) AS ''QtyP1'', SUM(T0.SalesAmt) AS ''SalesAmtP1'', 0 AS ''QtyP2'', 0 AS ''SalesAmtP2''
FROM [dbo].[VWAJ_SALANALYSIS] T0
WHERE T0.DocDate BETWEEN ''2016-01-01'' AND ''2016-03-31''
AND (
(('','' + RTRIM(T0.BPP) + '','') LIKE ''%,'' + @BPPA + '',%'') OR (('','' + RTRIM(T0.BPP) + '','') LIKE ''%,'' + @BPPB + '',%'')
)
GROUP BY T0.CustCode, T0.ItemCode
')

我究竟做错了什么？我相信我遗漏了一些引号，但不确定有多少以及为什么。提前致谢！阿杰

标签： tsqldynamic

解决方案

首先，您可能已经阅读了错误消息，它应该给您一个提示。

引号看起来没问题，主要问题是变量的作用域是声明它们的批处理。该EXEC语句在单独的批处理中执行sql字符串，因此在该批处理中找不到您的@BPPA变量@BPPB。您必须拆分字符串以从调用批处理中获取值：

DECLARE @BPPA AS varchar(5) = 'BPP2'
DECLARE @BPPB AS varchar(5) = 'BPP4'

EXEC('
SELECT T0.CustCode AS ''CustCode'', MAX(T0.CustName) AS ''CustName'', T0.ItemCode AS ''ItemCode'', MAX(T0.ItemName) AS ''ItemName'', MAX(T0.BPP) AS ''BPP'',
SUM(T0.Qty) AS ''QtyP1'', SUM(T0.SalesAmt) AS ''SalesAmtP1'', 0 AS ''QtyP2'', 0 AS ''SalesAmtP2''
FROM [dbo].[VWAJ_SALANALYSIS] T0
WHERE T0.DocDate BETWEEN ''2016-01-01'' AND ''2016-03-31''
AND (
(('','' + RTRIM(T0.BPP) + '','') LIKE ''%,''' + @BPPA + ''',%'') OR (('','' + RTRIM(T0.BPP) + '','') LIKE ''%,''' + @BPPB + ''',%'')
)
GROUP BY T0.CustCode, T0.ItemCode
')

对于参数的传递，您应该查看系统存储过程sp_executesql。

添加的示例： 使用sp_executesql，您的 EXEC 语句可能如下所示：

EXEC sp_executesql N'
SELECT T0.CustCode AS ''CustCode'', MAX(T0.CustName) AS ''CustName'', T0.ItemCode AS ''ItemCode'', MAX(T0.ItemName) AS ''ItemName'', MAX(T0.BPP) AS ''BPP'',
SUM(T0.Qty) AS ''QtyP1'', SUM(T0.SalesAmt) AS ''SalesAmtP1'', 0 AS ''QtyP2'', 0 AS ''SalesAmtP2''
FROM [dbo].[VWAJ_SALANALYSIS] T0
WHERE T0.DocDate BETWEEN ''2016-01-01'' AND ''2016-03-31''
AND (
(('','' + RTRIM(T0.BPP) + '','') LIKE ''%,'' + @BPPA + '',%'') OR (('','' + RTRIM(T0.BPP) + '','') LIKE ''%,'' + @BPPB + '',%'')
)
GROUP BY T0.CustCode, T0.ItemCode
'
, N'@BPPA nvarchar(5), @BPPB nvarchar(5)'
, @BPPA = N'BPP2', @BPPB = N'BPP4'

除了常量之外，您还可以像这样传递变量中的值（替换我的示例脚本的最后一行）：

, @BPPA = @BPPA, @BPPB = @BPPB

推荐阅读