下面的動態 SQL 查詢（我通過EXECUTE sp_executesql @sql;Azure Synapse 專用 SQL 池中的 SSMS 執行）回傳隨機語法錯誤，例如
第 105 行，第 44 列的決議錯誤：“log_Case”附近的語法不正確。
但是，如果我在 SSMS 中執行此查詢的非動態版本，它就可以正常作業。我考慮/嘗試但沒有成功的事情：
- 使用 Notepad 查找特殊字符或非 Windows 行尾
- 查詢的檢查長度低于 8000（nvarchar 的最大值）
任何人都知道我的動態查詢有什么問題以及為什么會失敗？這讓我很頭疼。
DECLARE @sql nvarchar(max);
DECLARE @clientstaging nvarchar(50) = 'staging';
SET @SQL = 'insert into ' @clientstaging '.log_CaseSLAChanges (
id,
caseDocumentType,
clientOrganisation,
billDate,
billNumber,
serviceDescription,
positionChanged,
servicePeriod,
currency,
prices,
totalPrice,
netPrice,
vatPrice,
vatRate,
vatNr,
taxNr,
creditor
)
SELECT
c.id id,
t.title,
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''clientOrganisation'') > 0
THEN 1
ELSE 0 END "clientOrganisation",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''billDate'') > 0
THEN 1
ELSE 0 END "billDate",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''billNumber'') > 0
THEN 1
ELSE 0 END "billNumber",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''serviceDescription'') > 0
THEN 1
ELSE 0 END "serviceDescription",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND ((l.fieldKey = ''Position'') OR (
l.fieldKey in (''description'', ''amount'', ''unit'', ''unitPrice'', ''totalPrice'', ''vatPrice'', ''vatRate'')
AND l.typeID = 1))) > 0
THEN 1
ELSE 0 END "positionChanged",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''servicePeriod'') > 0
THEN 1
ELSE 0 END "servicePeriod",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''currency'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "currency",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey IN (''totalPrice'', ''netPrice'', ''vatPrice'', ''vatRate'') AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "prices",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''totalPrice'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "totalPrice",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''netPrice'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "netPrice",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''vatPrice'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "vatPrice",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''vatRate'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "vatRate",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''vatNr'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "vatNr",
CASE WHEN (SELECT COUNT(*)
FROM ' @clientstaging '.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''taxNr'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "taxNr",
CASE WHEN (select COUNT(*)
FROM ' @clientstaging '.backend_CaseData d
JOIN ' @clientstaging '.backend_CaseCreditorData cdc on d.capturedCaseDataCreditorID = cdc.id
JOIN ' @clientstaging '.backend_CaseCreditorData cdd on d.caseCreditorDataID = cdd.id
WHERE d.caseID = c.id AND (cdc.normalizedReducedName != cdd.normalizedReducedName OR
cdc.normalizedStreetAndNumber != cdd.normalizedStreetAndNumber)
) > 0
THEN 1
ELSE 0 END "creditor"
FROM ' @clientstaging '.backend_Case c
JOIN ' @clientstaging '.backend_CaseProcess p ON p.caseID = c.id
JOIN ' @clientstaging '.backend_CaseData d on c.id = d.caseID
JOIN ' @clientstaging '.md_CaseDocumentType t ON t.id = d.caseDocumentTypeID
WHERE c.uploadedByFastLane = 0
AND p.processStepID != 0
and c.deleted = 0
and c.archived = 1';
EXECUTE sp_executesql @sql;
uj5u.com熱心網友回復：
除錯動態 SQL 最簡單的方法是先執行PRINT/SELECT陳述句。然后，您可以在將解決方案傳播到生成動態陳述句的 SQL 之前除錯該 SQL 并解決問題。通常您會發現問題非常簡單，例如難以在文字字串中發現的印刷錯誤；例如缺少空格/換行符，或前導/尾隨分隔符。花時間讓非動態陳述句首先作業非常重要，好像這不起作用，動態陳述句將沒有機會正常作業。
如果你做了PRINT/SELECT宣告，你會注意到它被截斷為 4,000 個字符，因為你的文字只是一個nvarchar(4000). CAST/CONVERT一部分到 a MAX：
DECLARE @sql nvarchar(max);
DECLARE @clientstaging sysname = N'staging';
SET @SQL = CONVERT(nvarchar(MAX),N'insert into ') QUOTENAME(@clientstaging) N'.log_CaseSLAChanges (
id,
caseDocumentType,
clientOrganisation,
billDate,
billNumber,
serviceDescription,
positionChanged,
servicePeriod,
currency,
prices,
totalPrice,
netPrice,
vatPrice,
vatRate,
vatNr,
taxNr,
creditor
)
SELECT
c.id id,
t.title,
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''clientOrganisation'') > 0
THEN 1
ELSE 0 END "clientOrganisation",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''billDate'') > 0
THEN 1
ELSE 0 END "billDate",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''billNumber'') > 0
THEN 1
ELSE 0 END "billNumber",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''serviceDescription'') > 0
THEN 1
ELSE 0 END "serviceDescription",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND ((l.fieldKey = ''Position'') OR (
l.fieldKey in (''description'', ''amount'', ''unit'', ''unitPrice'', ''totalPrice'', ''vatPrice'', ''vatRate'')
AND l.typeID = 1))) > 0
THEN 1
ELSE 0 END "positionChanged",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND l.fieldKey = ''servicePeriod'') > 0
THEN 1
ELSE 0 END "servicePeriod",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''currency'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "currency",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey IN (''totalPrice'', ''netPrice'', ''vatPrice'', ''vatRate'') AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "prices",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''totalPrice'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "totalPrice",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''netPrice'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "netPrice",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''vatPrice'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "vatPrice",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''vatRate'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "vatRate",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''vatNr'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "vatNr",
CASE WHEN (SELECT COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.log_CaseChangesLog l
WHERE l.caseID = c.id AND l.caseProcessStepID != 1 AND
l.fieldKey = ''taxNr'' AND l.typeID = 0) > 0
THEN 1
ELSE 0 END "taxNr",
CASE WHEN (select COUNT(*)
FROM ' QUOTENAME(@clientstaging) N'.backend_CaseData d
JOIN ' QUOTENAME(@clientstaging) N'.backend_CaseCreditorData cdc on d.capturedCaseDataCreditorID = cdc.id
JOIN ' QUOTENAME(@clientstaging) N'.backend_CaseCreditorData cdd on d.caseCreditorDataID = cdd.id
WHERE d.caseID = c.id AND (cdc.normalizedReducedName != cdd.normalizedReducedName OR
cdc.normalizedStreetAndNumber != cdd.normalizedStreetAndNumber)
) > 0
THEN 1
ELSE 0 END "creditor"
FROM ' QUOTENAME(@clientstaging) N'.backend_Case c
JOIN ' QUOTENAME(@clientstaging) N'.backend_CaseProcess p ON p.caseID = c.id
JOIN ' QUOTENAME(@clientstaging) N'.backend_CaseData d on c.id = d.caseID
JOIN ' QUOTENAME(@clientstaging) N'.md_CaseDocumentType t ON t.id = d.caseDocumentTypeID
WHERE c.uploadedByFastLane = 0
AND p.processStepID != 0
and c.deleted = 0
and c.archived = 1';
EXECUTE sys.sp_executesql @sql;
另外，作為旁注，我懷疑您是否需要所有這些子查詢；他們的表現會很糟糕。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/478222.html