Dynamic SQL in Synapse dedicated pool fails, but non-dynamic query works-CodePudding

The below dynamic SQL query, which I execute via EXECUTE sp_executesql @sql; through SSMS in an Azure Synapse dedicated SQL Pool, returns random syntax errors, such as

Parse error at line: 105, column: 44: Incorrect syntax near 'log_Case'.

However, if I execute a non-dynamic version of this query in SSMS it works just fine. Things that I considered/tried without success:

Find special characters or non-windows line endings with Notepad
Check length of query is below 8000 (max of nvarchar)

Anyone has an idea what is wrong with my dynamic query and why it fails? This gives me a headache.

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;

CodePudding user response：

The easiest way to debug dynamic SQL is to PRINT/SELECT the statement first. Then you can debug that SQL and solve the problem before propagating the solution to your SQL that generates the dynamic statement. Often you'll find that the problems are quite simple, such as a typographical error that is difficult to spot in the literal strings; for example a missing whitespace/linebreak, or leading/trailing delimiters. Taking the time to get the non-dynamic statement working first is really important, as if that doesn't work the dynamic one will have no chance of working correctly.

If you did PRINT/SELECT the statement, you'd note it was truncated to 4,000 characters as your literal is only an nvarchar(4000). CAST/CONVERT part of it to 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;

Also, as a side note, I doubt you need all those subqueries; they will be awful for performance.