SQL Server : Using Quotes In Linked Server Queries

Doing an OpenQuery across a linked server can get ugly very fast and it is often best to avoid them.

The basic syntax of an OpenQuery is :

SELECT * FROM OPENQUERY(YourLinkedServerName, 'SELECT FIELD1, FIELD2 FROM YOURTABLE')

From a coding perspective, this gets ugly if you have to incorporate string parameters; thus, utilizing many quotes.  For Example:

SELECT * FROM OPENQUERY(YourLinkedServerName, 'SELECT FIELD1, FIELD2 FROM YOURTABLE WHERE FIELD1 = ''ID105'' ')

Microsoft made this easier with the built-in stored procedure that allows you to avoid multi-layered quotes:  sp_executesql

The above can become:

DECLARE @PARAM char(5)

SELECT  @PARAM = 'CA105'

EXEC YourLinkedServer.master.dbo.sp_executesql

N'SELECT FIELD1, FIELD2 FROM YOURTABLE WHERE FIELD1 = @p1',

N'@p1 char(5)',

@PARAM

But, what if your WHERE clause becomes:

WHERE FIELD2 in (''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'')

There are multiple ways to go about this but here is one cheap method for dealing with the quotes:

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