使用动态查询在openquery中转义单引号 [英] Escape single quote in openquery using dynamic query
问题描述
我需要使用参数@PickedDate从链接的服务器中检索数据.如果我跳过@A和@B,则查询工作正常,但由于缺少单引号,它始终会返回错误.请告知,谢谢.
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.
查询:
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)
链接服务器"LinkedServerName"的OLE DB访问接口"MSDASQL"返回了消息"[Sybase] [ODBC驱动程序] [SQL Anywhere]"AAA"列未找到".Msg 7321,第16级,状态2,第1行准备查询"
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"
用于针对链接服务器"LinkedServerName"的OLE DB提供程序"MSDASQL"执行.
for execution against OLE DB provider "MSDASQL" for linked server "LinkedServerName".
推荐答案
您需要在变量周围使用单引号,因为您正试图使它们成为字符串文字.但同时也使您变得复杂的是,您试图在一个字符串中创建一个SQL语句,该字符串中包含另一个SQL语句.因此,您需要将行读为:
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. :)
最终,您需要构建一个包含此有效SQL语法的字符串:
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')
内部SQL字符串中的AAA和BBB周围需要两个引号,因为它也是字符串中的SQL代码.因此,您需要双双引号,才能在要构建的主字符串中获得双引号.
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.
这篇关于使用动态查询在openquery中转义单引号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!