2018年2月22日 星期四

[MSSQL] QUOTED_IDENTIFIER參數使用-解決Dynamic SQL使用兩個單引號問題

Dynamic SQL是以指令組合出SQL語法之作法,由於一般查詢文字資料時,需以單引號來分隔(包夾),當轉換為Dynamic SQL時,則必須將原本單引號轉換成兩組單引號,因此需額外處理並且常混淆,尤其是應用於OPENQERY情況下,可嘗試使用QUOTED_IDENTIFIER設定參數,可使用雙引號為字串分隔識別碼

SET QUOTED_IDENTIFIER ON    --可以用雙引號來分隔識別碼,文字則必須用單引號來分隔。
SET QUOTED_IDENTIFIER OFF  --識別碼不能附加引號,文字可以用單引號或雙引號來分隔。

DECLARE @SQL      nvarchar(4000)
DECLARE @TableName varchar(10)

SET @TableName='spt_values'
-----------------------------------------------------
--方法(1). set quoted_identifier on (預設值)
--         常用方法,Dynamic SQL中的single quote需以個single quote進行轉換
-----------------------------------------------------
SET @SQL=N'set quoted_identifier on
SELECT COUNT(*) Cols
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE 1=1
    AND C.TABLE_CATALOG = ''master''
    AND C.TABLE_NAME    = ''' + @TableName + ''''
EXEC sp_executesql @SQL

-----------------------------------------------------
--方法(2). set quoted_identifier off
--         Dynamic SQL中的single quote直接換成double quote
-----------------------------------------------------
--測試 2-1
SET @SQL=N'set quoted_identifier off
SELECT COUNT(*) Cols
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE 1=1
    AND C.TABLE_CATALOG = ''master''
    AND C.TABLE_NAME    = "' + @TableName + '"'

EXEC sp_executesql @SQL

--測試 2-2
SET @SQL=N'set quoted_identifier on
SELECT COUNT(*) Cols
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE 1=1
    AND C.TABLE_CATALOG = ''master''
    AND C.TABLE_NAME    = ''' + @TableName + ''''
EXEC sp_executesql @SQL

以上結果均為6。


沒有留言:

張貼留言