动态 TSQL 中的引号错误

Question

我尝试了许多帖子来解决下面的错误，但我被卡住了。如果有人能在这里引导我朝着正确的方向前进，将不胜感激。

-- 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
')

我做错了什么？我相信我错过了一些引号，但不确定有多少以及为什么。 提前感谢！ AJ

Answer 1

首先，您可以阅读错误消息，这应该给您一个提示。

引号看起来不错，主要问题是变量的范围是声明它们的批次。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

Answer 2

我认为您可以在下面尝试此代码。我现在无法访问SQL Server:)

DECLARE @BPPA AS nvarchar(5)
SET @BPPA = '''%,BPP2,%'''
DECLARE @BPPB AS nvarchar(5) 
SET @BPPB = '''%,BPP4,%'''
DECLARE @sSQL  AS varchar(750)
SET @sSQL = '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  N' + @BPPA + ') OR (('',''' + RTRIM(T0.BPP) + ''','') LIKE N' + @BPPB + ') ) GROUP BY T0.CustCode, T0.ItemCode ')
EXEC (@sSQL)

希望这能帮助你