CODEWITHSUNDEEP
sqlsql-serversql-server-2012
SQLLearner
SQLLearner

Reputation: 79

Dynamic SQL Execution Issue

I am getting Quotes error while Executing below Quotes.I replaced hard-code value with double quotes

 declare @query nvarchar(MAX)
    set @query = 'SELECT PID AS EID,
    ''637'' + p.SSN + SPACE(9- LEN(RTRIM(LTRIM(ISNULL(p.SSN,'')))))
    + ''98''
    + CONVERT(VARCHAR(6), GETDATE(), 12)
    + SPACE(9)
    + ''A1''
    + @Str1
    + @Str2
    + ISNULL(LEFT(UPPER(@Str1),30),'') + SPACE(30- LEN(RTRIM(LTRIM(ISNULL(LEFT(@Str1,30),'''')))))
    + ISNULL(LEFT(UPPER(@Str2),30),'') + SPACE(30- LEN(RTRIM(LTRIM(ISNULL(LEFT(@Str2,30),'''')))))
    + SPACE(1)
    + SPACE(8) AS Data, GETDATE() AS CreatedOn
    INTO #Temp
    FROM dbo.PERSON p'

    exec sp_executesql @query;



INSERT INTO  dbo.RKS_TransactionData
SELECT * FROM #Temp

Upvotes: 1

Views: 51

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

The problem with isnull() you need '''' in isnull() (already you have done but partially) :

 declare @query nvarchar(MAX)

 set @query = 'SELECT PID AS EID,
    ''637'' + p.SSN + SPACE(9- LEN(RTRIM(LTRIM(ISNULL(p.SSN,'''')))))
    + ''98''
    + CONVERT(VARCHAR(6), GETDATE(), 12)
    + SPACE(9)
    + ''A1''
    + @Str1
    + @Str2
    + ISNULL(LEFT(UPPER(@Str1),30),'''') + SPACE(30- LEN(RTRIM(LTRIM(ISNULL(LEFT(@Str1,30),'''')))))
    + ISNULL(LEFT(UPPER(@Str2),30),'''') + SPACE(30- LEN(RTRIM(LTRIM(ISNULL(LEFT(@Str2,30),'''')))))
    + SPACE(1)
    + SPACE(8) AS Data, GETDATE() AS CreatedOn
    INTO #Temp
    FROM dbo.PERSON p'

    print @query -- see how query looks or try to compile query

    exec sp_executesql @query;

Upvotes: 1

Related Questions