动态sql对报价有疑问 [英] dynamic sql doubt on quotation
问题描述
- 我读了一些文章,发现
'''anurag''' - 这在动态sql中将被视为'anurag'< br $>
声明 @ var varchar (max)
set @ var = ' select * from tbl_user_master其中first_name喜欢' + ' ''%anurag%'''
print @ var
exec ( @ var )
- 我得到的结果
-------------- -------------------------------------------------- --------------
- 但为什么我在下面的查询中收到错误
声明 @ var varchar (max)
set @ var = ' select * from tbl_user_master where first_name比如'''%anurag%' '''
print @ var
exec ( @ var )
--Msg 207,16级,状态1,第3行
无效的列名'anurag'。
- 我是新手..请指导我。
- 我认为两者都是一样的,两者都会给我相同的结果
不,他们不是不一样!
第一个例子:
喜欢'+'''%anurag%'''
^ ^^^ ^^^ - ---关闭第二个字符串
| ||| || -----一对引号的第二个字符\
| ||| | - 这些创建一个'字符
| ||| | ------一对引号的第一个字符/
| ||| ---------------一对引号的第二个字符\
| || - 这些创建一个'字符
| || ----------------一对引号的第一个字符/
| | -----------------打开第二个字符串
| ---------------------关闭第一个字符串在第二个例子中:
喜欢'''%anurag%''''
||| ||| | -----或者这......
||| ||| -------或者这......
||| || --------或者这......
||| | --------- SQL不打算看这个...
||| ERK! SQL NO了解! **从开始重做** ***出于奶酪错误***
||| ------------------第一个字符串结束
|| -------------------一对引号的第二个字符\
| - 这些创建一个'字符
| --------------------一对引号的第一个字符/
--i have read some article and found that
''' anurag ''' --this in dynamic sql will be treated as 'anurag'
declare @var varchar(max)
set @var='select * from tbl_user_master where first_name like'+ '''%anurag%'''
print @var
exec (@var)
--i get the result
------------------------------------------------------------------------------
--but why i get error on below query
declare @var varchar(max)
set @var='select * from tbl_user_master where first_name like '''%anurag%''' '
print @var
exec (@var)
--Msg 207, Level 16, State 1, Line 3
Invalid column name 'anurag'.
-- i m new to it.. kindly guide me.
--i thought both are same and both will give me the same result
No, they aren't the same!
The in first example:
like' + '''%anurag%''' ^ ^^^ ^^^---- Close second string | ||| ||----- 2nd character of a pair of quotes\ | ||| | - These create a single ' character | ||| |------ 1st character of a pair of quotes/ | |||--------------- 2nd character of a pair of quotes\ | || - These create a single ' character | ||---------------- 1st character of a pair of quotes/ | |----------------- Open second string |--------------------- Close first stringIn the second example:
like '''%anurag%''' ' ||| ||| |----- Or this... ||| |||------- Or this... ||| ||-------- Or this... ||| |--------- SQL not going to look at this... ||| ERK! SQL NO UNDERSTAND! **REDO FROM START** ***OUT OF CHEESE ERROR*** |||------------------ End of first string ||------------------- 2nd character of a pair of quotes\ | - These create a single ' character |-------------------- 1st character of a pair of quotes/
这篇关于动态sql对报价有疑问的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!