SQLServer:商量EXEC與sp_executesql的差別詳解。本站提示廣大學習愛好者：（SQLServer:商量EXEC與sp_executesql的差別詳解）文章只能為提供參考，不一定能成為您想要的結果。以下是SQLServer:商量EXEC與sp_executesql的差別詳解正文
摘要
1,EXEC的應用
2，sp_executesql的應用
MSSQL為我們供給了兩種靜態履行SQL語句的敕令，分離是EXEC和sp_executesql;平日,sp_executesql則更具有優勢，它供給了輸出輸入接口，而EXEC沒有。還有一個最年夜的利益就是應用sp_executesql，可以或許重用履行籌劃，這就年夜年夜供給了履行機能(關於這個我在前面的例子中會詳加解釋)，還可以編寫更平安的代碼。EXEC在某些情形下會更靈巧。除非您有使人佩服的來由應用EXEC，否側盡可能應用sp_executesql.
1,EXEC的應用
EXEC敕令有兩種用法，一種是履行一個存儲進程，另外一種是履行一個靜態的批處置。以下所講的都是第二種用法。
上面先應用EXEC演示一個例子,代碼1
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：
EXEC('SELECT TOP('+ CAST(@TopCount AS VARCHAR(10)) +')* FROM '+QUOTENAME(@TableName) +' ORDER BY ORDERID DESC');
SQL編譯器就會報錯，編譯欠亨過，而假如我們如許：
EXEC(@sql+@sql2+@sql3);
編譯器就會經由過程；所以最好的做法是把代碼結構到一個變量中，然後再把該變量作為EXEC敕令的輸出參數，如許就不會受限制了；
EXEC不供給接口
這裡的接口是指，它不克不及履行一個包括一個帶變量符的批處置，這裡乍一聽似乎不明確，沒關系，我鄙人面有一個實例，您一看就曉得甚麼意思.
DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'Orders';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = @OrderID ORDER BY ORDERID DESC'
EXEC(@sql);
症結就在SET @sql這一句話中，假如我們運轉這個批處置，編譯器就會發生一下毛病
Msg 137, Level 15, State 2, Line 1
必需聲明標質變量 "@OrderID"。
應用EXEC時，假如您想拜訪變量，必需把變量內容串連到靜態構建的代碼字符串中，如：SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = '+CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'
串連變量的內容也存在機能方面的弊病。SQL Server為每個的查詢字符串創立新的履行籌劃，即便查詢形式雷同也是如許。為演示這一點，先清空緩存中的履行籌劃
DBCC FREEPROCCACHE (這個不是本文所觸及的內容，您可以檢查MS的MSDN)
http://msdn.microsoft.com/zh-cn/library/ms174283.aspx
將代碼1運轉3次，分離對@OrderID 付與上面3個值，10251，10252，10253。然後應用上面的代碼查詢
SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cach%' AND sql NOT LIKE '%sys.%'
點擊F5運轉，就會湧現上面如圖所示的查詢成果：
我們可以看到，每履行一次都要發生一次的編譯，履行籌劃沒有獲得充足重用。
EXEC除不支撐靜態批處置中的輸出參數外，他也不支撐輸入參數。默許情形下，EXEC把查詢的輸入前往給挪用者。例以下面代碼前往Orders表中一切的記載數
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT COUNT(ORDERID) FROM Orders';
EXEC(@sql);
但是，假如你要把輸入前往給挪用批處置中的變量，工作就沒有那末簡略了。為此，你必需應用INSERT EXEC語法把輸入拔出到一個目的表中，然後從這表中獲得值後賦給該變量，就像如許：
DECLARE @sql NVARCHAR(MAX),@RecordCount INT
SET @sql = 'SELECT COUNT(ORDERID) FROM Orders';
CREATE TABLE #T(TID INT);
INSERT INTO #T EXEC(@sql);
SET @RecordCount = (SELECT TID FROM #T)
SELECT @RecordCount
DROP TABLE #T
2,sp_executesql的應用
sp_executesql敕令在SQL Server中引入的比EXEC敕令晚一些，它重要為重用履行籌劃供給更好的支撐。
為了和EXEC作一個光鮮的比較,我們看看假如用代碼1的代碼，把EXEC換成sp_executesql,看看能否獲得我們所希冀的成果
DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT ,@sql2 NVARCHAR(MAX);
SET @TableName = 'Orders ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC'
EXEC sp_executesql @sql
留意最初一行；
現實證實可以運轉；
sp_executesql供給接口
sp_executesql敕令比EXEC敕令更靈巧，由於它供給一個接口，該接口及支撐輸出參數也支撐輸入參數。這功效使你可以創立帶參數的查詢字符串，如許便可以比EXEC更好的重用履行籌劃,sp_executesql的組成與存儲進程異常類似，分歧的地方在於你是靜態構建代碼。它的組成包含：代碼快，參數聲明部門，參數賦值部門。說了這麼多，照樣看看它的語法吧
EXEC sp_executesql
@stmt = <statement>,--相似存儲進程主體
@params = <params>, --相似存儲進程參數部門
<params assignment> --相似存儲進程挪用
@stmt參數是輸出的靜態批處置，它可以引入輸出參數或輸入參數，和存儲進程的主體語句一樣，只不外它是靜態的，而存儲進程是靜態的，不外你也能夠在存儲進程中應用sp_executesql;
@params參數與界說輸出/輸入參數的存儲進程頭相似，現實上和存儲進程頭的語法完整一樣；
@<params assignment> 與挪用存儲進程的EXEC部門相似。
為了解釋sp_executesql對履行籌劃的治理優於EXEC,我將應用後面評論辯論EXEC時用到的代碼。
DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'Orders ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE OrderID = @OID ORDER BY ORDERID DESC'
EXEC sp_executesql
@stmt = @sql,
@params = N'@OID AS INT ',
@OID = @OrderID
在挪用該代碼和檢討它生成的履行籌劃前，先清空緩存中的履行籌劃；
DBCC FREEPROCCACHE
將下面的靜態代碼履行3次，每次履行都付與@OrderID 分歧的值，然後查詢sys.syscacheobjects表，並留意它的輸入，優化器只創立了一個備用籌劃，並且該籌劃被重用的3次
SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cache%' AND sql NOT LIKE '%sys.%' AND sql NOT LIKE '%sp_executesql%'
點擊F5運轉，就會湧現以下表所示的成果；
sq_executesql的另外一個與其接口有關的壯大功效是，你可使用輸入參數為挪用批處置中的 變量前往值。應用該功效可以免用暫時表前往數據，從而獲得更高效的代碼和更少的從新編譯。界說和應用輸入參數的語法與存儲進程相似。也就是說，你須要在聲明參數時指定OUTPUT子句。例如，上面的靜態代碼簡略的演示了若何從靜態批處置中應用輸入參數@p把值前往到內部批處置中的變量@i.
DECLARE @sql AS NVARCHAR(12),@i AS INT;
SET @sql = N' SET @p = 10';
EXEC sp_executesql
@stmt = @sql,
@params = N'@p AS INT OUTPUT',
@p = @i OUTPUT
SELECT @i
該代碼前往輸入10
以上就是EXEC和sp_executesql的重要差別，假如列位看官認為哪纰謬或許表達不清晰的，還請多多指出^_^
