Escape single quote in openquery using dynamic query

Question

I need to retrieve data from a linked server using a parameter, @PickedDate for example. The query works fine if I skip @A and @B, but it always returns an error due to a missing single quote. Please advise, thanks.

The query:

Declare @OPENQUERY nvarchar(500), @TSQL nvarchar(max), @LinkedServer nvarchar(20), @A varchar(5), @B varchar(5), @PickedDate varchar(8)
Set @PickedDate = '20150501'
Set @A = 'AAA'
Set @B = 'BBB'
Set @LinkedServer = 'LinkedServerName'
Set @OPENQUERY = 'Select * From Openquery('+ @LinkedServer + ','''
Set @TSQL = 'SELECT cases.casenum, user.username, code
            From cases
            Inner join user
            On cases.casenum = user.user_id
            Where cases.date_opened > DateAdd(day,1-datepart(dw,Convert(date,' + @PickedDate + ')), Convert(date,' + @PickedDate + '))
            And cases.date_opened <= DateAdd(day,8-datepart(dw,Convert(date,' + @PickedDate + ')), Convert(date,' + @PickedDate + '))
            And cases.code IN (' + @A +', ' + @B + ')
            ORDER BY casenum'')'
Exec (@Openquery+@TSQL)

OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[Sybase][ODBC Driver][SQL Anywhere]Column 'AAA' not found". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "

  SELECT cases.casenum, username, code
  From cases
  Inner join user
  On cases.casenum = user.user_id
  Where cases.date_opened > 
    DateAdd(day,1-datepart(dw,Convert(date,20150501)), Convert(date,20150501))
  And cases.date_opened <= 
    DateAdd(day,8-datepart(dw,Convert(date,20150501)), Convert(date,20150501))
  And cases.code IN (AAA, BBB)
  ORDER BY casenum" 

for execution against OLE DB provider "MSDASQL" for linked server "LinkedServerName".

Esparto

how about And cases.code IN ('+ chr(39) + @A +', ' + chr(39)+ @B + chr(39)+ ') – Quilting

char(39) #14657556 – Quilting

It behaves the same as (''' + @A +''', ''' + @B + '''). Still gives "Incorrect syntax near 'AAA'." – Esparto

Answer 1

You need single quotes around your variables since you are trying to make them string literals. But also complicating it is the fact that you are trying to create a SQL statement in a string that includes another SQL statement in a string. So you need to make your line read like:

And cases.code IN (''''' + @A +''''', ''''' + @B + ''''')

You need two sets of double quotes so that the string literal inside of your string literal is interpreted correctly. Huh? Right. :)

Ultimately you need to build a string that has this valid SQL Syntax in it:

Select * From Openquery(LinkedServerName,'SELECT cases.casenum, user.username, code
            From cases
            Inner join user
            On cases.casenum = user.user_id
            Where cases.date_opened > DateAdd(day,1-datepart(dw,Convert(date,20150501)), Convert(date,20150501))
            And cases.date_opened <= DateAdd(day,8-datepart(dw,Convert(date,20150501)), Convert(date,20150501))
            And cases.code IN (''AAA'', ''BBB'')
            ORDER BY casenum')

You need two quotes around the AAA and BBB in your inner SQL string because it is also SQL code inside a string. So you need double double quotes to get double quotes inside your main string you are building.

Straw

That's the answer! Thank you very much, Brain. – Esparto

I meant to use @A and @B to escape the single quotes. In this case, I can simply use cases.code IN (''''AAA'''', ''''B'''') then. – Esparto

Answer 2

Using Brian Pressler response i suggest to create a function to format the params if you have various openquery calls, sometimes quoting a lot could be eyebreaker.

CREATE FUNCTION [dbo].[Ufn_QuoteFormat]   
( @param varchar(200) --Modify accord your requirement)     
RETURNS varchar(208)
AS    
BEGIN   
  DECLARE @SingleQuote char(1) = CHAR(39)   
  RETURN  @SingleQuote + @SingleQuote + @param + @SingleQuote +@SingleQuote   
END

Then you can use it in your openqueries like this:

Declare @OPENQUERY nvarchar(500), @TSQL nvarchar(max), @LinkedServer nvarchar(20), @A varchar(5), @B varchar(5), @PickedDate varchar(8)
Set @PickedDate = '20150501'
Set @A = 'AAA'
Set @B = 'BBB'
Set @LinkedServer = 'LinkedServerName'
Set @OPENQUERY = 'Select * From Openquery('+ @LinkedServer + ','''
Set @TSQL = 'SELECT cases.casenum, user.username, code
            From cases
            Inner join user
            On cases.casenum = user.user_id
            Where cases.date_opened > DateAdd(day,1-datepart(dw,Convert(date,' + @PickedDate + ')), Convert(date,' + @PickedDate + '))
            And cases.date_opened <= DateAdd(day,8-datepart(dw,Convert(date,' + @PickedDate + ')), Convert(date,' + @PickedDate + '))
            And cases.code IN (' + [dbo].[Ufn_QuoteFormat](@A) +', ' + [dbo].[Ufn_QuoteFormat](@B) + ')
            ORDER BY casenum'')'
Exec (@Openquery+@TSQL)

Unconsidered

Answer 3

You need to add the single quotes so they will appear in the dynamic query context. Try changing that line to this:

And cases.code IN (''' + @A +''', ''' + @B + ''')

Varnish

That doesn't work. It returns error: Incorrect syntax near 'AAA'. – Esparto

When you get the error, what do you get when you PRINT @Openquery+@TSQL ? – Varnish