CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
WRD299
WRD299

Reputation: 37

Amount of single quotes when running dynamic SQL with variable

I am having a real hard time sorting out the number of ' I should have within the following SQL statement:

declare @sql varchar(max)
declare @LetterID varchar(max) = 'c01as1'
set @sql = 
'SELECT fltr.tency_seq_no FROM OPENQUERY(loopback, 
    ''SET FMTONLY OFF; EXEC BST.[LET].[LETTERBUILD] @LetterCode = 
        ''''\\SVR-QL4APPLIVE\QLSHAREPOINT\LETTERS\DATAFILES\
            '''+@LetterID+'''
        .csv''''
    WITH RESULT SETS (tency_seq_no VARCHAR(255))''
) AS fltr'
exec (@sql)

Current error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'c01as1'

Just to clarify, the error is referring to the @LetterID variable within the dynamic SQL , not when declaring the parameter

Print of @ SQL

SELECT fltr.tency_seq_no 
FROM OPENQUERY(loopback, 'SET FMTONLY OFF; EXEC BST.[LET].[LETTERBUILD] @LetterCode = ''\\SVR-QL4APPLIVE\QLSHAREPOINT\LETTERS\DATAFILES\'c01as1'.csv''
     WITH RESULT SETS (tency_seq_no VARCHAR(255));'
    ) AS fltr

Any help would be appreciated!

Upvotes: 2

Views: 315

Answers (2)

solutionist
solutionist

Reputation: 168

When I ran into this difficulty a few months ago, I created a function that would perform a double-up on the quotes for dynamic SQL. Instead of searching the string manually each time for quotes needing to be doubled, this scalar function can perform this task. This can prevent potentially cluttering up the script when future modifications are performed, such as adding additional variables, as well as improving readability.

Function as follows:

CREATE FUNCTION dbo.fn_duplicateQuotes
    (@string varchar(max),
    @level int)
RETURNS varchar(max)
AS
BEGIN
    /*Doubles-up quotation marks for nested dynamic SQL
      level can be set greater than 1 to add additional doubled-up quotes
      for further nested dynamic SQL*/

    /*Double up quotes*/
        set @string = REPLACE(@string, '''', REPLICATE('''', (@level) * 2))

    /*Return Value*/
        return @string
END

Dynamic SQL as follows:

declare @SQL nvarchar(max)
declare @LetterID varchar(max) = 'c01as1'

set @SQL = 'SET FMTONLY OFF; EXEC BST.[LET].[LETTERBUILD] @LetterCode = 
              ''\\SVR-QL4APPLIVE\QLSHAREPOINT\LETTERS\DATAFILES\' + @LetterID + '.csv''
             WITH RESULT SETS (tency_seq_no VARCHAR(255));'

set @SQL = 'SELECT fltr.tency_seq_no FROM OPENQUERY(loopback,
        ''' + dbo.fn_duplicateQuotes(@SQL, 1) + '''
        ) AS fltr'

print @SQL
exec (@SQL)

Print of @SQL returns:

SELECT fltr.tency_seq_no FROM OPENQUERY(loopback,
        'SET FMTONLY OFF; EXEC BST.[LET].[LETTERBUILD] @LetterCode = 
              ''\\SVR-QL4APPLIVE\QLSHAREPOINT\LETTERS\DATAFILES\c01as1.csv''
             WITH RESULT SETS (tency_seq_no VARCHAR(255));'
        ) AS fltr

Upvotes: 1

Thailo
Thailo

Reputation: 1424

I think the error has got something to do with unwanted line breaks etc in the original code. Try this instead:

DECLARE @sql VARCHAR(MAX);
DECLARE @LetterID VARCHAR(MAX) = 'c01as1';

SET @sql = 'SELECT fltr.tency_seq_no FROM OPENQUERY(loopback, 
    ''SET FMTONLY OFF; EXEC BST.[LET].[LETTERBUILD] @LetterCode = ''''\\SVR-QL4APPLIVE\QLSHAREPOINT\LETTERS\DATAFILES\'
           + @LetterID + '.csv''''
    WITH RESULT SETS (tency_seq_no VARCHAR(255))''
) AS fltr';

EXEC (@sql);

Upvotes: 1

Related Questions