为什么我们在动态sql查询中使用多个单引号 [英] why we use multiple single quotes in dynamic sql query

问题描述

  exec （
 ' '  SELECT   CASE   SERVERPROPERTY （' ''' IsFullTextInstalled  ' '''）
  WHEN   1  那么 
  CASE  DatabaseProperty（ DB_NAME （ DB_ID （）），' ''' IsFulltextEnabled ' '''）
  WHEN   1  那么  1  
  ELSE   0  
  END  
  ELSE   0  
 < span class =code-keyword> END  
 ' ' 
）

可以解释为什么在上面的查询中，他们在select语句中使用了多个单引号？

解决方案

简单：单引号是字符串的开始和结束指示符，因此如果要在字符串中包含单引号，则必须同时使用其中两个。

因此，如果你想要生成一个包含单引号的字符串，那很简单：

'AB''CD'

但是你正在尝试生成一个将被执行的字符串，所以它也需要双引号以防止它被当作终结符：

 '' 'AB''''CD'''

传递一个包含

'AB''CD'的字符串

执行。

对你提供的代码的观察 - 当我将它粘贴到查询窗口时我得到错误

引用：

消息156，级别15，状态1，行2

关键字'SELECT'附近的语法不正确。

消息102，级别15，状态1，第2行

'IsFullTextInstalled'附近的语法不正确。

代码开头和结尾的那些额外双引号不应该存在。如果删除，代码将生成文本

  SELECT   CASE   SERVERPROPERTY （' ' IsFullTextInstalled ' '）
  WHEN   1  那么 
  CASE  DatabaseProperty（ DB_NAME （ DB_ID （）），' ' IsFulltextEnabled ' '）
  WHEN   1  那么  1  
  ELSE   0  
  END  
  ELSE   0  
  END  

仍会产生错误。我认为代码应该是

  exec （
 ' < span class =code-string> SELECT CASE SERVERPROPERTY（''IsFullTextInstalled''）
 WHEN 1 THEN 
 CASE DatabaseProperty（DB_NAME（DB_ID（）），''IsFulltextEnabled''）
当1那么1 
 ELSE 0 
 END 
 ELSE 0 
 END 
'）

我有时采用的避免长引号单引号的技巧是使用 CHAR（39）来表示动态查询中需要的单引号例如

  DECLARE   @ DynamicQuery   Nvarchar （max）= 
 '  SELECT CASE SERVERPROPERTY（' +  CHAR （ 39 ）+ '  IsFullTextInstalled' +  CHAR （ 39 ）+ ' ）
 WHEN 1 THEN 
 CASE DatabaseProperty（DB_NAME（DB_ID（）），' +  CHAR （ 39 ）+ '  IsFulltextEnabled' +  CHAR （ 39 ）+ ' ）
 WHEN 1 THEN 1 
 ELSE 0 
 END 
 ELSE 0 
 END 
' 
  EXEC  sp_executesql  @ DynamicQuery  

请参阅此链接以获取答案br



http://www.sqlteam.com/forums/topic.asp?TOPIC_ID = 179130 [ ^ ]



另一件要理解的事情

< br /> 
< br /> 
来自mytable的SELECT列其中col ='Lifco''< br /> 
< br /> 
当您指定具有单引号的值时，需要加倍它< br /> 
< br /> 
来自mytable的SELECT列，其中col ='Lifco'的'< br /> 
< br /> 
以下内容可能会有所帮助（运行并查看结果）< br /> 
< br /> 
 SELECT''，''''，''''''''' ''''''，''''''''''< br /> 
< br /> 
当您使用静态SQL并在单引号中表达值时first和last sigle引号指定该值是一个字符串。然后在这些单引号中，每个双引号表示单个单引号< br /> 
< br /> 
当您使用Dynamic sql时，第一个和最后一个sigle引号指定它​​是动态sql 。然后在这些单引号中，每个双引号都指定它是一个字符串。然后在这些单引号中，每四个单引号代表一个单引号< br /> 
< br /> 
运行并查看结果< br /> 
< br /> 
 EXEC（'SELECT''''''''''''''''''''''''''''''''''' '' '' '' '' '' '' '' ''， '' '' '' '' '' '' '' '' '' '''）

exec(
''SELECT CASE SERVERPROPERTY(''''IsFullTextInstalled'''')
	WHEN 1 THEN 
		CASE DatabaseProperty (DB_NAME(DB_ID()), ''''IsFulltextEnabled'''')
		WHEN 1 THEN 1
		ELSE 0
		END
	ELSE 0
	END
''
)

can any one explain why, in the above query, they are using multiple single quotes in the select statement?

解决方案

Simple: single quote is the string start-and-end indicator, so if you want to include a single quote in the string, you have to use two of them together.
So if you are trying to generate a string that contains a single quote it's simple:

'AB''CD'

But you are trying to generate a string that will be executed, so it needs double quotes as well to prevent it being taken as a terminator there:

'''AB''''CD'''

Passes a string containing

'AB''CD'

for execution.

An observation on the code you presented - when I paste it into a query window I get errors

Quote:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'IsFullTextInstalled'.

Those extra double quotes at the start and end of the code should not be there. If removed the code produces the text

SELECT CASE SERVERPROPERTY(''IsFullTextInstalled'')
    WHEN 1 THEN
        CASE DatabaseProperty (DB_NAME(DB_ID()), ''IsFulltextEnabled'')
        WHEN 1 THEN 1
        ELSE 0
        END
    ELSE 0
    END

which still produces the error. I think the code should read

exec(
'SELECT CASE SERVERPROPERTY(''IsFullTextInstalled'')
    WHEN 1 THEN
        CASE DatabaseProperty (DB_NAME(DB_ID()), ''IsFulltextEnabled'')
        WHEN 1 THEN 1
        ELSE 0
        END
    ELSE 0
    END
    ')

A technique I sometimes employ to avoid long streams of single quotes is to use CHAR(39) to represent the single quote that needs to be in the dynamic query E.g.

DECLARE @DynamicQuery Nvarchar(max) =
'SELECT CASE SERVERPROPERTY(' + CHAR(39) + 'IsFullTextInstalled' + CHAR(39) + ')
    WHEN 1 THEN
        CASE DatabaseProperty (DB_NAME(DB_ID()),' + CHAR(39) + 'IsFulltextEnabled' + CHAR(39) + ')
        WHEN 1 THEN 1
        ELSE 0
        END
    ELSE 0
    END
'
EXEC sp_executesql  @DynamicQuery

Refer this link for your answer bro

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=179130[^]

another thing to Understand this

<br />
<br />
SELECT columns from mytable where col ='Lifco's'<br />
<br />
When you specify a value which has single quote, you need to double it<br />
<br />
SELECT columns from mytable where col ='Lifco''s'<br />
<br />
The following may be helpful (Run and see the result)<br />
<br />
SELECT '','''','''''','''''''',''''''''''<br />
<br />
When you use a static sql and express a value in a single quote then first and last sigle quotes specify that the value is a string. Then within those single quotes every double single quotes represent a single single quote<br />
<br />
When you use a Dynamic sql then first and last sigle quotes specify that it is a dynamic sql. Then within those single quotes every double single quotes specify that it is a string.Then within those single quotes every four single quotes represent a single single quote<br />
<br />
Run and see the result<br />
<br />
EXEC('SELECT '''','''''''','''''''''''','''''''''''''''',''''''''''''''''''''')

这篇关于为什么我们在动态sql查询中使用多个单引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！