RiCo技術農場

當我們有使用到dynamic sql時， set quoted_identifier off 真是好選項，

如下sample 將可以感受到維護性提高不少

 

set quoted_identifier on

declare @mysql nvarchar(4000)
declare @empid varchar(10)
,@sdate varchar(10)

set @empid='a1083'
set @sdate='2013/10/01'

set @mysql=N'select top(20000) emp_id,emp_name,sdate
 from DOORDATA where emp_id='''+@empid+ ''' and sdate>='''+@sdate+''''


 exec sp_executesql  @mysql

 

 

set quoted_identifier off

declare @mysql nvarchar(4000)
declare @empid varchar(10)
,@sdate varchar(10)

set @empid='a1083'
set @sdate='2013/10/01'

set @mysql=N' set quoted_identifier off
select top(20000) emp_id,emp_name,sdate
from DOORDATA where emp_id= "'+@empid+'" and sdate>= "'+@sdate+ '"'


 exec sp_executesql  @mysql

組sql時最討厭單引號所帶來的困擾，但透過 set quoted_identifier off 

只需透過 " 將可當作字串邊界符號(如[]一樣)。