Azure Synapse专用SQL池动态SQL执行失败，但非动态查询正常运行

我一眼就发现了问题的核心：你的动态SQL字符串里混进了HTML转义字符，比如>（本应是>）和"（本应是双引号）。这些字符在非动态查询中可能已经被SSMS自动转换为合法的SQL语法符号，但在动态拼接的字符串里，SQL Server会把它们当成普通文本内容直接参与语法解析，这就是为什么会随机抛出类似"Incorrect syntax near 'log_Case'"的错误。

具体问题拆解： #

• >会被SQL Server识别为字符串>，而非比较运算符>，直接导致条件判断的语法完全失效
• "clientOrganisation"这种写法会被当成字符串字面量，而非列别名，SQL Server无法识别这种格式的别名定义

修复方案： #

1. 替换所有HTML转义字符：

   • 把所有>替换成实际的比较运算符>
   • 把"列名"替换成SQL兼容的别名格式，比如用方括号[clientOrganisation]（如果列名无特殊字符，直接省略引号也可行）

2. 优化动态SQL拼接（可选但推荐）：
   使用QUOTENAME()函数处理架构名，既能避免SQL注入风险，又能确保拼接的对象名符合SQL语法规范。另外，重复的子查询逻辑可以考虑重构，提升查询性能。

修改后的完整代码： #

DECLARE @sql nvarchar(max);
DECLARE @clientstaging nvarchar(50) = 'staging';
SET @SQL = 'insert into ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@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 ' + QUOTENAME(@clientstaging) + '.backend_CaseData d JOIN ' + QUOTENAME(@clientstaging) + '.backend_CaseCreditorData cdc on d.capturedCaseDataCreditorID = cdc.id JOIN ' + QUOTENAME(@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 ' + QUOTENAME(@clientstaging) + '.backend_Case c 
JOIN ' + QUOTENAME(@clientstaging) + '.backend_CaseProcess p ON p.caseID = c.id 
JOIN ' + QUOTENAME(@clientstaging) + '.backend_CaseData d on c.id = d.caseID 
JOIN ' + QUOTENAME(@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;

额外调试技巧： #

下次遇到动态SQL语法问题时，可以先执行PRINT @sql;把拼接好的SQL语句打印出来，然后直接复制到SSMS中执行，这样能快速定位错误位置，比盲目排查高效得多。

内容的提问来源于stack exchange，提问作者Jens