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

Question

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;

score 1 · Accepted Answer · answered May 19 '22 at 14:40

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.

Thank you very much. Afterall, I had the right problem in mind, but did not properly check it with a print statement. I will keep that in mind as I am new to dynamic queries. Thanks for the tip! Also thanks for the input regarding the subqueries. In fact, I am only moving old code over to Synapse AS IS, since it will be completely restructured in the near future. For now, the performance is not an issue. — Jens, May 19 '22 at 15:05
`PRINT` in this case, wouldn't have actually caught the problem, as `PRINT` is limited to 8,000 bytes. — Thom A, May 19 '22 at 15:06

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

1 Answers1