'Unclosed quotation mark after the character string' error in a dynamic query

Question

My script below gives an error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '000000'.

Msg 105, Level 15, State 1, Line 26
Unclosed quotation mark after the character string ' AND M.ACTIVE_FLAG=1 '

My code:

DECLARE @Command NVARCHAR(MAX);

SET @Command = 'SELECT 
        S.VOUCHER, '+
        ''''+'1'+''''+ ' UPLOAD_TYPE, '+
        ''''+'UP0000001'+''''+ ' UPLOAD_ID,
        S.BRANCH BRANCH,
        --HARDCODE SEGMENT 
        CASE WHEN S.SEGMENT= ''
        THEN '+''''+'000000'+''''+'
        ELSE S.SEGMENT 
        END SEGMENT,
        M.SMART_TOC TOC,
        1 VERSION,
        S.ACCOUNT, 
        S.TYPE,
        M.SMART_ACCOUNT_ID ACCOUNT_ID,
        M.SMART_ACCOUNT_DESCRIPTIONS,
        S.YEAR YEAR,
        S.MONTH MONTH, 
        S.DEBIT *-1 DEBIT_AMOUNT,
        S.CREDIT CREDIT_AMOUNT,'+
        ''''+'DIRECT.UPLOAD'+''''+ ' UPLOADED_BY,
        GETDATE() LAST_UPDATE
    FROM #JM_DATA S,AAD_MASTER.DBO.MASTER_COA M
    WHERE 
        S.ACCOUNT = M.ACCOUNT_ID
        AND S.TYPE = M.TYPE
        AND M.IS_PARENT='+''''+'0'+''''+' AND M.ACTIVE_FLAG=1'

EXEC sp_executesql @Command;

PRINT @Command and examine the output. There is some extra ' or missing ' somewhere. — Vladimir Baranov
Your time would be better spent printing the output and inspecting it, rather than posting a question here. — Nick.McDermaid
Evolve - NO ONE should be using old-style joins via the where clause. And there seems to be no reason to use dynamic sql here since there is nothing dynamic about the query. — SMor

Mukesh Arora Mukesh Arora · Accepted Answer · 2020-03-18T05:20:09

Try this -

CASE WHEN S.SEGMENT= '' was incorrect, it should be CASE WHEN S.SEGMENT= ''''. The best way to identify the problem is to use print @Command and run the printed query to know the issue.

Declare @Command nvarchar(max)
 SET @Command = 'SELECT 
        S.VOUCHER, '+
        ''''+'1'+''''+ ' UPLOAD_TYPE, '+
        ''''+'UP0000001'+''''+ ' UPLOAD_ID,
        S.BRANCH BRANCH,
        --HARDCODE SEGMENT 
        CASE WHEN S.SEGMENT= ''''
        THEN '+''''+'000000'+''''+'
        ELSE S.SEGMENT 
        END SEGMENT,
        M.SMART_TOC TOC,
        1 VERSION,
        S.ACCOUNT, 
        S.TYPE,
        M.SMART_ACCOUNT_ID ACCOUNT_ID,
        M.SMART_ACCOUNT_DESCRIPTIONS,
        S.YEAR YEAR,
        S.MONTH MONTH, 
        S.DEBIT *-1 DEBIT_AMOUNT,
        S.CREDIT CREDIT_AMOUNT,'+
        ''''+'DIRECT.UPLOAD'+''''+ ' UPLOADED_BY,
        GETDATE() LAST_UPDATE
    FROM #JM_DATA S,AAD_MASTER.DBO.MASTER_COA M
    WHERE 
        S.ACCOUNT = M.ACCOUNT_ID
        AND S.TYPE = M.TYPE
        AND M.IS_PARENT='+''''+'0'+''''+' AND M.ACTIVE_FLAG=1'

EXEC sp_executesql @Command

'Unclosed quotation mark after the character string' error in a dynamic query

1 Answers