sp_executesql

iSQlServer發表於2009-10-15
SQL

執行可以多次重複使用或動態生成的 Transact-SQL 語句或批處理。Transact-SQL 語句或批處理可以包含嵌入引數。

Transact-SQL 語法約定

語法


sp_executesql [ @stmt = ] stmt
[ 
     {, [@params=] N'@parameter_name data_type [ [ OUT [ PUT ][,...n]' } 
      {, [ @param1 = ] 'value1' [ ,...n ] }
]

備註

在批處理、名稱作用域和資料庫上下文方面，sp_executesql 與 EXECUTE 的行為相同。sp_executesql stmt 引數中的 Transact-SQL 語句或批處理在執行 sp_executesql 語句時才編譯。隨後，將編譯 stmt 中的內容，並將其作為執行計劃執行。該執行計劃獨立於名為 sp_executesql 的批處理的執行計劃。sp_executesql 批處理不能引用呼叫 sp_executesql 的批處理中宣告的變數。sp_executesql 批處理中的本地遊標或變數對呼叫 sp_executesql 的批處理是不可見的。對資料庫上下文所作的更改只在 sp_executesql 語句結束前有效。

如果只更改了語句中的引數值，則 sp_executesql 可用來代替儲存過程多次執行 Transact-SQL 語句。因為 Transact-SQL 語句本身保持不變，僅引數值發生變化，所以 SQL Server 查詢優化器可能重複使用首次執行時所生成的執行計劃。

注意：
如果語句字串中的物件名不是完全限定名，則該執行計劃不會被重複使用。

 

sp_executesql 支援與 Transact-SQL 字串分開的引數值的設定，如以下示例所示。


 
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT * FROM AdventureWorks.HumanResources.Employee 
     WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;

輸出引數也可用於 sp_executesql。以下示例從 AdventureWorks.HumanResources.Employee 表中檢索名稱，並將其返回在輸出引數中。


 
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title) 
   FROM AdventureWorks.HumanResources.Employee
   WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

替換 sp_executesql 中的引數的能力，與使用 EXECUTE 語句執行字串相比，有下列優點：

  • 因為在 sp_executesql 字串中，Transact-SQL 語句的實際文字在兩次執行之間並未改變，所以查詢優化器應該能將第二次執行中的 Transact-SQL 語句與第一次執行時生成的執行計劃匹配。因此，SQL Server 不必編譯第二條語句。
  • Transact-SQL 字串只生成一次。
  • 整數引數按其本身格式指定。不需要轉換為 Unicode。

引數

[ @stmt = ] stmt

包含 Transact-SQL 語句或批處理的 Unicode 字串。stmt 必須是可以隱式轉換為 ntext 的 Unicode 常量或變數。不允許使用更復雜的 Unicode 表示式（例如使用 + 運算子連線兩個字串）。不允許使用字元常量。如果指定常量，則必須使用 N 作為字首。例如，Unicode 常量 N'sp_who' 是有效的，但是字元常量 'sp_who' 則無效。字串的大小僅受可用資料庫伺服器記憶體限制。

注意：
在 64 位伺服器上，字串大小受 nvarchar(max) 大小的限制。

 

注意：
stmt 可以包含與變數名形式相同的引數，例如： N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

 

stmt 中包含的每個引數在 @params 引數定義列表和引數值列表中均必須有對應項。

[ @params = ] N'@parameter_name data_type [ ,... n ] '

包含 stmt 中嵌入的所有引數定義的字串。該字串必須是可隱式轉換為 ntext 的 Unicode 常量或變數。每個引數定義由引數名稱和資料型別組成。n 是表示附加引數定義的佔位符。在 stmt 中指定的每個引數必須在 @params 中定義。如果 stmt 中的 Transact-SQL 語句或批處理不包含引數，則不需要 @params。該引數的預設值為 NULL。

[ @param1 = ] 'value1'

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

OUTPUT

指示該引數是輸出引數。除非此過程是公共語言執行時 (CLR) 過程，否則 text、ntext 和 image 引數可用作 OUTPUT 引數。使用 OUTPUT 關鍵字的輸出引數可以為遊標佔位符，CLR 過程除外。

n

附加引數值的佔位符。這些值只能為常量或變數，不能是很複雜的表示式（例如函式）或使用運算子生成的表示式。

返回程式碼值

0（成功）或非零（失敗）

結果集

從生成 SQL 字串的所有 SQL 語句返回結果集。

許可權

要求具有 public 角色的成員身份。

示例

A. 執行簡單的 SELECT 語句

以下示例將建立並執行一個簡單的 SELECT 語句，其中包含名為 @level 的嵌入引數。


 
EXECUTE sp_executesql 
          N'SELECT * FROM AdventureWorks.HumanResources.Employee 
          WHERE ManagerID = @level',
          N'@level tinyint',
          @level = 109;

B. 執行動態生成的字串

以下示例顯示使用 sp_executesql 執行動態生成的字串。該示例中的儲存過程用於向一組表中插入資料，這些表用於劃分一年的銷售資料。一年中的每個月均有一個表，格式如下：


 
CREATE TABLE May1998Sales
    (OrderID int PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate  datetime NULL
        CHECK (DATEPART(yy, OrderDate) = 1998),
    OrderMonth int
        CHECK (OrderMonth = 5),
    DeliveryDate datetime  NULL,
        CHECK (DATEPART(mm, OrderDate) = OrderMonth)
    )

有關從這些分割槽表中檢索資料的詳細資訊，請參閱在分割槽檢視中修改資料。

每個表的名稱由月份名的前三個字母、年度的四位數字和常量 Sales 組成。表名可以從訂單日期動態生成。


 
/* Get the first three characters of the month name. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
/* Concatenate the four-digit year; cast as character. */
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
/* Concatenate the constant 'Sales'. */
'Sales'

此示例儲存過程將動態生成並執行 INSERT 語句，以便向正確的表中插入新訂單。此示例使用訂單日期生成應包含資料的表的名稱，然後將此名稱併入 INSERT 語句中。

注意：
這是一個簡單的 sp_executesql 示例。該示例不包含錯誤檢查以及業務規則檢查，如確保訂單號在各個表之間不重複。

 

 


 
CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT,
                 @PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT

-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
       /* Build the name of the table. */
       SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
       CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
       'Sales' +
       /* Build a VALUES clause. */
       ' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
       ' @InsOrdMonth, @InsDelDate)'

/* Set the value to use for the order month because
   functions are not allowed in the sp_executesql parameter
   list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)

EXEC sp_executesql @InsertString,
     N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
       @InsOrdMonth INT, @InsDelDate DATETIME',
     @PrmOrderID, @PrmCustomerID, @PrmOrderDate,
     @OrderMonth, @PrmDeliveryDate

GO

在該過程中使用 sp_executesql 比使用 EXECUTE 執行字串更有效。使用 sp_executesql 時，只生成 12 個版本的 INSERT 字串，每個月的表對應 1 個字串。使用 EXECUTE 時，因為引數值不同，每個 INSERT 字串均是唯一的。儘管兩種方法生成的批處理數相同，但因為 sp_executesql 生成的 INSERT 字串都相似，所以查詢優化器更有可能重複使用執行計劃。

原文地址：http://www.cnblogs.com/hack/archive/2009/10/15/1583634.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-616630/，如需轉載，請註明出處，否則將追究法律責任。

相關文章