1,EXEC的使用

2，sp_executesql的使用

MSSQL為我們提供了兩種動態(tài)執(zhí)行SQL語句的命令，分別是EXEC和sp_executesql;通常,sp_executesql則更具有優(yōu)勢，它提供了輸入輸出接口，而EXEC沒有。還有一個最大的好處就是利用sp_executesql，能夠重用執(zhí)行計劃，這就大大提供了執(zhí)行性能(對于這個我在后面的例子中會詳加說明)，還可以編寫更安全的代碼。EXEC在某些情況下會更靈活。除非您有令人信服的理由使用EXEC，否側(cè)盡量使用sp_executesql.

1,EXEC的使用

EXEC命令有兩種用法，一種是執(zhí)行一個存儲過程，另一種是執(zhí)行一個動態(tài)的批處理。以下所講的都是第二種用法。

下面先使用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括號中只允許包含一個字符串變量，但是可以串聯(lián)多個變量，如果我們這樣寫EXEC：

EXEC('SELECT TOP('+ CAST(@TopCount AS VARCHAR(10)) +')* FROM '+QUOTENAME(@TableName) +' ORDER BY ORDERID DESC')SQL編譯器就會報錯，編譯不通過，而如果我們這樣：EXEC(@sql+@sql2+@sql3); 編譯器就會通過；所以最佳的做法是把代碼構(gòu)造到一個變量中，然后再把該變量作為EXEC命令的輸入?yún)?shù)，這樣就不會受限制了；EXEC不提供接口這里的接口是指，它不能執(zhí)行一個包含一個帶變量符的批處理，這里乍一聽好像不明白，不要緊，我在下面有一個實例，您一看就知道什么意思.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); 關(guān)鍵就在SET @sql這一句話中，如果我們運行這個批處理，編譯器就會產(chǎn)生一下錯誤

Msg 137, Level 15, State 2, Line 1必須聲明標量變量 '@OrderID'。

使用EXEC時，如果您想訪問變量，必須把變量內(nèi)容串聯(lián)到動態(tài)構(gòu)建的代碼字符串中，如：SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = '+CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'

串聯(lián)變量的內(nèi)容也存在性能方面的弊端。SQL Server為每一個的查詢字符串創(chuàng)建新的執(zhí)行計劃，即使查詢模式相同也是這樣。為演示這一點，先清空緩存中的執(zhí)行計劃

DBCC FREEPROCCACHE (這個不是本文所涉及的內(nèi)容，您可以查看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運行，就會出現(xiàn)下面如圖所示的查詢結(jié)果：

我們可以看到，每執(zhí)行一次都要產(chǎn)生一次的編譯，執(zhí)行計劃沒有得到充分重用。

EXEC除了不支持動態(tài)批處理中的輸入?yún)?shù)外，他也不支持輸出參數(shù)。默認情況下，EXEC把查詢的輸出返回給調(diào)用者。例如下面代碼返回Orders表中所有的記錄數(shù)

DECLARE @sql NVARCHAR(MAX) SET @sql = 'SELECT COUNT(ORDERID) FROM Orders'; EXEC(@sql); 然而，如果你要把輸出返回給調(diào)用批處理中的變量，事情就沒有那么簡單了。為此，你必須使用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命令晚一些，它主要為重用執(zhí)行計劃提供更好的支持。

為了和EXEC作一個鮮明的對比,我們看看如果用代碼1的代碼，把EXEC換成sp_executesql,看看是否得到我們所期望的結(jié)果

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命令更靈活，因為它提供一個接口，該接口及支持輸入?yún)?shù)也支持輸出參數(shù)。這功能使你可以創(chuàng)建帶參數(shù)的查詢字符串，這樣就可以比EXEC更好的重用執(zhí)行計劃,sp_executesql的構(gòu)成與存儲過程非常相似，不同之處在于你是動態(tài)構(gòu)建代碼。它的構(gòu)成包括：代碼快，參數(shù)聲明部分，參數(shù)賦值部分。說了這么多，還是看看它的語法吧

EXEC sp_executesql

@stmt = <statement>,--類似存儲過程主體

@params = <params>, --類似存儲過程參數(shù)部分

<params assignment> --類似存儲過程調(diào)用

@stmt參數(shù)是輸入的動態(tài)批處理，它可以引入輸入?yún)?shù)或輸出參數(shù)，和存儲過程的主體語句一樣，只不過它是動態(tài)的，而存儲過程是靜態(tài)的，不過你也可以在存儲過程中使用sp_executesql;

@params參數(shù)與定義輸入/輸出參數(shù)的存儲過程頭類似，實際上和存儲過程頭的語法完全一樣；

@<params assignment> 與調(diào)用存儲過程的EXEC部分類似。

為了說明sp_executesql對執(zhí)行計劃的管理優(yōu)于EXEC,我將使用前面討論EXEC時用到的代碼。

1: DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT2: SET @TableName = 'Orders '3: SET @OrderID = 102514: SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE OrderID = @OID ORDER BY ORDERID DESC'; 5: EXEC sp_executesql; 6: @stmt = @sql,; 7: @params = N'@OID AS INT ',; 8: @OID = @OrderID 在調(diào)用該代碼和檢查它生成的執(zhí)行計劃前，先清空緩存中的執(zhí)行計劃；

DBCC FREEPROCCACHE

將上面的動態(tài)代碼執(zhí)行3次，每次執(zhí)行都賦予@OrderID 不同的值，然后查詢sys.syscacheobjects表，并注意它的輸出，優(yōu)化器只創(chuàng)建了一個備用計劃，而且該計劃被重用的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運行，就會出現(xiàn)如下表所示的結(jié)果；

sq_executesql的另一個與其接口有關(guān)的強大功能是，你可以使用輸出參數(shù)為調(diào)用批處理中的變量返回值。利用該功能可以避免用臨時表返回數(shù)據(jù)，從而得到更高效的代碼和更少的重新編譯。定義和使用輸出參數(shù)的語法與存儲過程類似。也就是說，你需要在聲明參數(shù)時指定OUTPUT子句。例如，下面的靜態(tài)代碼簡單的演示了如何從動態(tài)批處理中利用輸出參數(shù)@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 OUTPUTSELECT @i該代碼返回輸出10 以上就是EXEC和sp_executesql的主要區(qū)別，如果各位看官覺得哪不對或者表達不清楚的，還請多多指出^_^