none
Is There A Cleaner Way to Handle Quotes in sp_executesql

    Question

  • I have a linked server query that passes many parameters as shown below.  I have to manipulate the quotes and such and am wondering if there is a cleaner way to write this query via sp_executesql ?    As is, the query runs fine.

    DECLARE @TSQL NVARCHAR(2000);
    DECLARE @CODES NVARCHAR(500);
    DECLARE @OPENQUERY NVARCHAR(100);
    DECLARE @LINKED_SQL NVARCHAR(2700)
    SET @CODES = '''ID105'',''ID107'',''ID109'',''ID112'',''ID116'',
    ''ID119'',''ID400'',''ID404'',''ID101'',''ID102'',''ID115'',''ID106'',''ID111'',''ID117'',''ID412'',''654Z '',''ID103'',''ID121'',''ID401'',''ID403'',
    ''ID407'',''ID108'',''ID113'',''ID114'',''ID120'',''ID123'',''ID405'',''ID409'',''ID100'',''ID110'',''ID124'',''ID408'',''ID104'',''ID118'',''ID406'',
    ''ID413''';
    SELECT @TSQL = 'SELECT … WHERE FIELD2 IN (‘+ @CODES + ')'	 
    -- CLEAN UP THE QUOTES FOR THE OPENQUERY
    -----------------------------------------------------                  
    SET @TSQL = REPLACE(@TSQL, '''','''''')  ;
    SET @TSQL = @TSQL + ''')';
    SET @OPENQUERY = 'SELECT * FROM OPENQUERY(GHDB, ''';
    SET @LINKED_SQL = (@OPENQUERY+@TSQL)
    EXEC sp_executesql @LINKED_SQL

    Thanks.

    Please Vote &/or "Mark As Answer" if this post is helpful to you. Thanks and happy coding :D

    Tuesday, July 02, 2013 3:54 PM

Answers

All replies