EXEC 和 SP_EXECUTESQL的區別

標籤：

摘要：

MSSQL為我們提供了兩種動態執行sql語句的命令：EXEC 和 SP_EXECUTESQL。通常SP_EXECUTESQL更具優勢，因為它提供了輸入輸出的介面，且能夠重用執行計畫，大大提高執行效率，而且不會導致SQL注入，比較安全，這些優勢都是EXEC所不具有的，EXEC通常用來執行預存程序。所以，除非有充分的理由，否則在執行動態SQL的時候盡量使用SP_EXECUTESQL。

 

用法舉例：

-----------EXEC：

DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = ‘Orders‘;
SET @OrderID = 10251;
SET @sql = ‘SELECT * FROM ‘+QUOTENAME(@TableName) +‘WHERE OrderID = ‘+CAST(@OrderID AS VARCHAR(10))+‘ ORDER BY ORDERID DESC‘
EXEC(@sql);

註：注意用EXEC的時候要有括弧且括弧中只允許有一個字串變數，也可以是由多個字串拼接而成的，例如：

EXEC(@[email protected][email protected]);

但是如下就會報錯。

EXEC(‘SELECT TOP(‘+ CAST(@TopCount AS VARCHAR(10)) +‘)* FROM ‘+QUOTENAME(@TableName) +‘ ORDER BY ORDERID DESC‘);

所以最佳實務是把所有代碼構造到一個字串變數中，然後EXEC（變數）。

 

-----------SP_EXECUTESQL：

先來看一下SP_EXECUTESQL的文法：

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

[ @stmt= ] statement

包含 Transact-SQL 陳述式或批處理的 Unicode 字串。@stmt 必須是 Unicode 常量或 Unicode 變數。  不允許使用更複雜的 Unicode 運算式（例如使用 + 運算子串連兩個字串）。不允許使用字元常量。 如果指定了 Unicode 常量，則必須使用 N 作為首碼， 字串的大小僅受可用資料庫伺服器記憶體限制， 在 64 位元伺服器中，字串大小限制為 2 GB，即 nvarchar(max) 的最大大小。 

[ @params= ] N‘@parameter_namedata_type [ ,... n ] ‘
一個字串，它包含 @stmt 中嵌入的所有參數的定義。 字串必須是 Unicode 常量或 Unicode 變數。 每個參數定義由參數名稱和資料類型組成。必須在 @params 中定義 @stmt中指定的每個參數。 如果 @stmt 中的 Transact-SQL 陳述式或批處理不包含參數，則不需要使用 @params。 該參數的預設值為 NULL。

[ @param1= ] ‘value1‘

參數字串中定義的第一個參數的值。  該值可以是 Unicode 常量，也可以是 Unicode 變數。  必須為 @stmt 中包含的每個參數提供參數值。  如果 @stmt 中的 Transact-SQL 陳述式或批處理沒有參數，則不需要這些值。

 

注意：參數傳遞可以選擇‘@name = value‘形式或者直接寫‘value‘， 但是一旦前面使用了 ‘@name = value‘ 形式，所有後續的參數就必須以 ‘@name = value‘ 的形式傳遞。如果需要傳出某個參數的值，需要在[ @param1= ] ‘value1‘後面加上OUTPUT。

舉例如下：

DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX)
SET @IN_Score = 90
SET @sql = ‘SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score‘
EXEC SP_EXECUTESQL @sql,N‘@Nums INT OUT,@Score INT‘,@OUT_Nums OUTPUT,@IN_Score
SELECT @OUT_Nums AS ‘人數‘

 

EXEC 和 SP_EXECUTESQL的區別