[MSSQL] sp_executesql動態執行SQL語法之指令

MSSQL提供EXEC和sp_executesql兩種動態執行SQL語法的指令，兩者最大差別在於sp_executesql提供輸出/入的指定參數介面，使用，提供更彈性、安全的執行方法，語法如下：

sp_executesql [ @stmt = ] statement  [     { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }        { , [ @param1 = ] 'value1' [ ,...n ] }  ]

以先前範例，取得master下系統資料表spt_values之欄位數量，執行結果同樣為6，T-SQL如下:

DECLARE @SQL         nvarchar(4000)

DECLARE @Cols      int

--0. 組合Dynamc SQL

SET @SQL=N'

SELECT @pCols=COUNT(*)

FROM INFORMATION_SCHEMA.COLUMNS C

WHERE 1=1

    AND C.TABLE_CATALOG = ''master''

    AND C.TABLE_NAME    = @pTableName

    '

--1. 執行Dynamc SQL   

EXEC sp_executesql @SQL

    , N'@pTableName varchar(20), @pCols int OUTPUT' --1. 變數宣告params

    , 'spt_values', @Cols OUTPUT                                --2. 指定資料值@param1='value1'

   

--2. 結果輸出  

SELECT @Cols

對於Stored Proecdure輸出/入參數之型態定義，請注意ORACLE與MSSQL資料庫兩者之差異，ORACLE僅需指定型態而不得指定其長度/精度，但MSSQL則否，以varchar(字元型態)如未指定則預設為長度為1，雖sp_executesql指令為系統命令仍適用。

可自行建立測試即可得知，如下

CREATE PROC spt_test

@IN VARCHAR

AS

BEGIN

SELECT @IN

END

EXEC spt_test 'A'

EXEC spt_test 'AB'