程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> 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運行,就會出現下面如圖所示的查詢結果:

dynmicsql1

我們可以看到,每執行一次都要產生一次的編譯,執行計劃沒有得到充分重用。
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運行,就會出現如下表所示的結果;

dynmicsql2

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的主要區別,如果各位看官覺得哪不對或者表達不清楚的,還請多多指出^_^

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved