[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。

參考資料: https://msdn.microsoft.com/zh-tw/library/ms174393.aspx