escape quotes inside quotes in TSQL string

Question

I'm trying to run the following TSQL statement on Microsoft SQL 2008

DECLARE @tmpMessage nvarchar(max)

SET @tmpMessage = 'select * from openquery(GLive,''select ID from Links WHERE [HREF] LIKE ''test'''')';

exec sp_executesql @tmpMessage

the above code doesnt work because the single quotes before test closes the main quotes around the second select statement select ID from Links ....

and yes i have to put my statement in a string first before executes it because openquery function wont allow me to do somethin like

select * from openquery(GLive,'select ID from Links WHERE [Href] LIKE ''' + @Var + ''''')

any suggesstions would be appreciated.

thanks in advance.

Answer 1

You might also try out the QUOTENAME command.

Answer 2

Here is a template that I use whenever I'm dealing with variables in an openquery statement to a linked server:

DECLARE @UniqueId int
, @sql varchar(500)
, @linkedserver varchar(30)
, @statement varchar(600)

SET @UniqueId = 2

SET @linkedserver = 'LINKSERV'
SET @sql = 'SELECT DummyFunction(''''' + CAST(@UniqueId AS VARCHAR(10))+ ''''') FROM DUAL'
SET @statement = 'SELECT * FROM OPENQUERY(' + @linkedserver + ', ' 
SET @Statement = @Statement + '''' +  @SQL + ''')'
EXEC(@Statement)