sqlserver 中EXEC和sp_executesql使用介紹

標籤：sqlserver   exec   sp_executesql   

sqlserver 中EXEC和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),@SqlNVARCHAR(MAX),@OrderID INT;

SET @TableName = ‘Orders‘;

SET @OrderID = 10251;

SET @sql = ‘SELECT * FROM ‘+QUOTENAME(@TableName) +‘WHEREOrderID = ‘+CAST(@OrderID AS VARCHAR(10))+‘ ORDER BY ORDERID DESC‘

EXEC(@sql);

註：這裡的EXEC括弧中只允許包含一個字串變數，但是可以串聯多個變數，如果我們這樣寫EXEC：

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

 

SQL編譯器就會報錯，編譯不通過，而如果我們這樣：

EXEC(@sql+@sql2+@sql3);

編譯器就會通過；

 

所以最佳的做法是把代碼構造到一個變數中，然後再把該變數作為EXEC命令的輸入參數，這樣就不會受限制了；

 

EXEC不提供介面

這裡的介面是指，它不能執行一個包含一個帶變數符的批處理，這裡乍一聽好像不明白，不要緊，我在下面有一個執行個體，您一看就知道什麼意思.

DECLARE @TableNameVARCHAR(50),@Sql NVARCHAR(MAX),@OrderIDINT;

SET @TableName =‘Orders‘;

SET @OrderID = 10251;

SET @sql =‘SELECT * FROM ‘+QUOTENAME(@TableName)+‘WHEREOrderID = @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。然後使用下面的代碼查詢

SELECTcacheobjtype,objtype,usecounts,sqlFROM sys.syscacheobjectsWHEREsqlNOTLIKE‘%cach%‘ANDsqlNOTLIKE‘%sys.%‘

點擊F5運行，就會出現下面的查詢結果：

我們可以看到，每執行一次都要產生一次的編譯，執行計畫沒有得到充分重用。

EXEC除了不支援動態批處理中的輸入參數外，他也不支援輸出參數。預設情況下，EXEC把查詢的輸出返回給調用者。例如下面代碼返回Orders表中所有的記錄數

DECLARE @sql NVARCHAR(MAX)

SET @sql =‘SELECT COUNT(ORDERID) FROM Orders‘;

EXEC(@sql);

然而，如果你要把輸出返回給調用批處理中的變數，事情就沒有那麼簡單了。為此，你必須使用INSERT EXEC文法把輸出插入到一個目標表中，然後從這表中擷取值後賦給該變數，就像這樣：

DECLARE @sql NVARCHAR(MAX),@RecordCountINT

SET @sql =‘SELECT COUNT(ORDERID) FROM Orders‘;

 

CREATETABLE #T(TIDINT);

INSERT INTO #T EXEC(@sql);

SET @RecordCount = (SELECT TIDFROM #T)

SELECT @RecordCount

DROPTABLE #T

 

2、sp_executesql的使用

sp_executesql命令在SQL Server中引入的比EXEC命令晚一些，它主要為重用執行計畫提供更好的支援。

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

DECLARE @TableNameVARCHAR(50),@sql NVARCHAR(MAX),@OrderIDINT ,@sql2 NVARCHAR(MAX);

SET @TableName =‘Orders ‘;

SET @OrderID = 10251;

SET @sql =‘SELECT * FROM ‘+QUOTENAME(@TableName)+‘ WHERE OrderID = ‘+CAST(@OrderIDASVARCHAR(50)) +‘ ORDER BY ORDERIDDESC‘

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時用到的代碼。

  1: DECLARE @TableName VARCHAR(50),@sqlNVARCHAR(MAX),@OrderID INT;

  2: SET @TableName = ‘Orders ‘;

  3: SET @OrderID = 10251;

  4: 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

在調用該代碼和檢查它產生的執行計畫前，先清空緩衝中的執行計畫；

DBCC FREEPROCCACHE

將上面的動態代碼執行3次，每次執行都賦予@OrderID 不同的值，然後查詢sys.syscacheobjects表，並注意它的輸出，最佳化器只建立了一個備用計劃，而且該計劃被重用的3次

SELECT cacheobjtype,objtype,usecounts,sqlFROM 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

 

sqlserver 中EXEC和sp_executesql使用介紹