SQL Server聯機叢書：執行預存程序

 EXECUTE

執行標量值的使用者定義函數、系統過程、使用者定義預存程序或擴充預存程序。同時支援 Transact-SQL 批處理內的字串的執行

若要喚醒調用函數，請使用 EXECUTE stored_procedure 中描述的文法。

文法

執行預存程序：

[ [ EXEC [ UTE ] ]     {          [ @return_status = ]             { procedure_name [ ;number ] | @procedure_name_var     }      [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ]         [ ,...n ]  [ WITH RECOMPILE ]

執行字串：

EXEC [ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )

參數

[ [ EXEC [ UTE ] ]     {          [ @return_status = ]             { procedure_name [ ;number ] | @procedure_name_var     }      [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ]         [ ,...n ]  [ WITH RECOMPILE ]

執行字串：

EXEC [ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )

參數

EXEC [ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )

參數

@return_status

是一個可選的整型變數，儲存預存程序的返回狀態。這個變數在用於 EXECUTE 語句前，必須在批處理、預存程序或函數中聲明過。

在用於喚醒調用標量值使用者定義函數時，@return_status 變數可以是任何純量資料型別。

procedure_name

是擬調用的預存程序的完全合法或者不完全合法的名稱。過程名稱必須符合標識符規則。有關更多資訊，請參見使用標識符。無論伺服器的字碼頁或排序方式如何，擴充預存程序的名稱總是區分大小寫。 

使用者可以執行在另一資料庫中建立的過程，只要該使用者擁有此過程或有在該資料庫中執行它的適當的許可權。使用者可以在另一台運行 Microsoft SQL Server 的伺服器上執行過程，只要該使用者有適當的許可權使用該伺服器（遠端存取），並能在資料庫中執行該過程。如果指定了伺服器名稱但沒有指定資料庫名稱，SQL Server 會在使用者預設的資料庫中尋找該過程。

;number

是可選的整數，用於將相同名稱的過程進行組合，使得它們可以用一句 DROP PROCEDURE 語句除去。該參數不能用於擴充預存程序。

在同一應用程式中使用的過程一般都以該方式組合。例如，在訂購應用程式中使用的過程可以 orderproc;1、orderproc;2 等來命名。DROP PROCEDURE orderproc 語句將除去整個組。在對過程分組後，不能除去組中的單個過程。例如，DROP PROCEDURE orderproc;2 是不允許的。有關過程組的更多資訊，請參見 CREATE PROCEDURE。

@procedure_name_var

是局部定義變數名，代表格儲存體過程名稱。

@parameter

是過程參數，在 CREATE PROCEDURE 語句中定義。參數名稱前必須加上符號 (@)。在以 @parameter_name = value 格式使用時，參數名稱和常量不一定按照 CREATE PROCEDURE 語句中定義的順序出現。但是，如果有一個參數使用 @parameter_name = value 格式，則其它所有參數都必須使用這種格式。

預設情況下，參數可為空白。如果傳遞 NULL 參數值，且該參數用於 CREATE 或 ALTER TABLE 語句中不允許為 NULL 的列（例如，插入至不允許為 NULL 的列），SQL Server 就會報錯。為避免將 NULL 參數值傳遞給不允許為 NULL 的列，可以在過程中添加程式設計邏輯或採用預設值（使用 CREATE 或 ALTER TABLE 語句中的 DEFAULT 關鍵字）。

value

是過程中參數的值。如果參數名稱沒有指定，參數值必須以 CREATE PROCEDURE 語句中定義的順序給出。

如果參數值是一個對象名稱、字串或通過資料庫名稱或所有者名稱進行限制，則整個名稱必須用單引號括起來。如果參數值是一個關鍵字，則該關鍵字必須用雙引號括起來。

如果在 CREATE PROCEDURE 語句中定義了預設值，使用者執行該過程時可以不必指定參數。如果該過程使用了帶 LIKE 關鍵字的參數名稱，則預設值必須是常量，並且可以包含 %、_、[ ] 及 [^] 萬用字元。

預設值也可以為 NULL。通常，流程定義會指定當參數值為 NULL 時應該執行的操作。

@variable

是用來儲存參數或者返回參數的變數。

OUTPUT

指定預存程序必須返回一個參數。該預存程序的匹配參數也必須由關鍵字 OUTPUT 建立。使用遊標變數作參數時使用該關鍵字。

如果使用 OUTPUT 參數，目的是在調用批處理或過程的其它語句中使用其傳回值，則參數值必須作為變數傳遞（即 @parameter = @variable）。如果一個參數在 CREATE PROCEDURE 語句中不是定義為 OUTPUT 參數，則對該參數指定 OUTPUT 的過程不能執行。不能使用 OUTPUT 將常量傳遞給預存程序；返回參數需要變數名稱。在執行過程之前，必須聲明變數的資料類型並賦值。返回參數可以是 text 或 image 資料類型以外的任意資料類型。

DEFAULT

根據過程的定義，提供參數的預設值。當過程需要的參數值沒有事先定義好的預設值，或缺少參數，或指定了 DEFAULT 關鍵字，就會出錯。

n

是預留位置，表示在它前面的項目可以多次重複執行。例如，EXECUTE 語句可以指定一個或者多個 @parameter、value 或 @variable。

WITH RECOMPILE

強制編譯新的計劃。如果所提供的參數為非典型參數或者資料有很大的改變，使用該選項。在以後的程式執行中使用更改過的計劃。該選項不能用於擴充預存程序。建議盡量少使用該選項，因為它消耗較多系統資源。

@string_variable

是局部變數的名稱。@string_variable 可以是 char、varchar、nchar 或 nvarchar 資料類型，最大值為伺服器的可用記憶體。如果字串長度超過 4,000 個字元，則把多個局部變數串聯起來用於 EXECUTE 字串。有關係統提供的 SQL Server 資料類型更多的資訊，請參見資料類型。

[N]'tsql_string'

是一個常量，tsql_string 可以是 nvarchar 或 varchar 資料類型。如果包含 N，則該字串將解釋為 nvarchar 資料類型，最大值為伺服器的可用記憶體。如果字串長度超過 4,000 個字元，則把多個局部變數串聯起來用於 EXECUTE 字串。

注釋

如果過程名稱的前三個字元為 sp_，SQL Server 會在 Master 資料庫中尋找該過程。如果沒能找到合法的過程名稱，SQL Server 會尋找所有者名稱為 dbo 的過程。若要將預存程序名稱解析為與系統預存程序同名的使用者定義預存程序，請提供一個完全合法的過程名稱。

參數可以通過利用 value 或 @parameter_name = value 來提供。參數不是事務的一個部分；因而如果事務中的參數值更改，且該事務在以後復原，該參數值不會退回到以前的值。返回給調用方的值總是過程返回時的值。

當一個預存程序調用另一個預存程序時，會產生嵌套。當調用的過程開始執行時，嵌套級會增加，當調用過程執行結束時，嵌套級則會減少。嵌套級最高為32級，超過32級時，會導致整個調用過程鏈失敗。當前的嵌套級儲存在 @@NESTLEVEL 函數中。

SQL Server 目前使用傳回值 0 到 -14 來表示預存程序的執行狀態。值 –15 到 -99 留作後用。有關保留的返回狀態值的列表的更多資訊，請參見 RETURN。

因為遠端預存程序和擴充預存程序不在事務的範圍中（除非在 BEGIN DISTRIBUTED TRANSACTION 語句中發出或者是和不同的配置選項一起使用），所以通過調用執行的命令不能復原。有關更多資訊，請參見系統預存程序和 BEGIN DISTRIBUTED TRANSACTION。

當使用遊標變數時，如果執行的過程傳遞一個分配有遊標的遊標變數，就會出錯。

在執行預存程序時，如果語句是批處理中的第一個語句，則不一定要指定 EXECUTE 關鍵字。

使用帶字串的 EXECUTE 命令

使用字串串聯運算子 (+) 為動態執行建立長字串。每個字串運算式可以是 Unicode 與 non-Unicode 資料類型的混合。

儘管每個 [N] 'tsql_string' 或 @string_variable 不得超過 8,000 個位元組，SQL Server 文法分析器中對這種串聯只進行邏輯處理而不佔用實體記憶體。例如，該語句決不會產生長 16,000 個串聯起來的字串：

EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')

在 EXECUTE 語句執行前，不會編譯 EXECUTE 語句內的語句。

資料庫環境的更改只在 EXECUTE 語句結束前有效。例如，在這個例子的 EXEC 後，資料庫環境是 master：

USE master EXEC ("USE pubs") SELECT * FROM authors

許可權

預存程序的 EXECUTE 許可權預設給該預存程序的所有者，該所有者可以將此許可權轉讓給其他使用者。當遇到 EXECUTE 語句時，即使 EXECUTE 語句是在預存程序中，也會檢查在 EXECUTE 字串內使用該語句的許可權。當運行一個執行字串的預存程序時，系統會在執行該過程的使用者環境中，而不是在建立該過程的使用者環境中檢查許可權。但是，如果某使用者擁有兩個預存程序，並且第一個程序呼叫第二個過程，則在第二個過程中不進行 EXECUTE 許可權檢查。

樣本A. 使用 EXECUTE 傳遞單個參數

showind 預存程序需要參數 (@tabname)，它是一個表的名稱。下面這個例子執行 showind 預存程序，以 titles 為參數值。

 

說明  showind 預存程序只是用來作為一個例子，pubs 資料庫並沒有此過程。

EXEC showind titles
在執行過程中變數可以顯式命名：EXEC showind @tabname = titles

如果這是 isql 指令碼或批處理中第一個語句，則 EXEC 語句可以省略：

showind titles
-或-showind @tabname = titles

B. 使用多個參數與一個輸出參數
這個例子執行 roy_check 預存程序，傳遞三個參數。第三個參數 @pc 是輸出參數。過程執行完後，返回變數可以從變數@percent
得到。
 
說明  roy_check 預存程序只是用作舉例，pubs 資料庫中並沒有此過程。
DECLARE @percent intEXECUTE roy_check 'BU1032', 1050, @pc = @percent OUTPUTSET Percent = @percent
C.使用帶一個變數的 EXECUTE 'tsql_string' 語句
這個例子顯示 EXECUTE 語句如何處理動態產生的、含有變數的字串。這個例子建立 tables_cursor 遊標來儲存所有使用者定義表

(type = U) 的列表。
說明  此例子只用作舉例。 
  

   

    

     
DECLARE tables_cursor CURSOR

   FOR

   SELECT name FROM sysobjects WHERE type = 'U'

OPEN tables_cursor

DECLARE @tablename sysname

FETCH NEXT FROM tables_cursor INTO @tablename

WHILE (@@FETCH_STATUS <> -1)

BEGIN

   /* A @@FETCH_STATUS of -2 means that the row has been deleted.

   There is no need to test for this because this loop drops all

   user-defined tables.   */.

   EXEC ('DROP TABLE ' + @tablename)

   FETCH NEXT FROM tables_cursor INTO @tablename

END

PRINT 'All user-defined tables have been dropped from the database.'

DEALLOCATE tables_cursor 
    
   
  
D.使用帶遠端預存程序的 EXECUTE 語句
這個例子在遠程伺服器 SQLSERVER1 上執行 checkcontract 預存程序，在 @retstat 中儲存返回狀態，說明運行成功或失敗。

    

     

      

       
DECLARE @retstat int 
EXECUTE @retstat = SQLSERVER1.pubs.dbo.checkcontract '409-56-4008' 
 
      
     
    
E. 使用帶擴充預存程序的 EXECUTE 語句
下例使用 xp_cmdshell 擴充預存程序列出副檔名為 .exe 的所有檔案的目錄。

    

     

      

       
USE master 
EXECUTE xp_cmdshell 'dir *.exe' 
 
      
     
    
F. 使用帶一個預存程序變數的 EXECUTE 語句
這個例子建立一個代表格儲存體過程名稱的變數。

    

     

      

       
DECLARE @proc_name varchar(30) 
SET @proc_name = 'sp_who' 
EXEC @proc_name 
 
      
     
    
G. 使用帶 DEFAULT 的 EXECUTE 語句
這個例子建立了一個預存程序，過程中第一個和第三個參數為預設值。當運行該過程時，如果調用時沒有傳遞值或者指定了預設值，

這些預設值就會賦給第一個和第三個參數。注意 DEFAULT 關鍵字有多種使用方法。

    

     

      

       
USE pubs 
IF EXISTS (SELECT name FROM sysobjects  
      WHERE name = 'proc_calculate_taxes' AND type = 'P') 
   DROP PROCEDURE proc_calculate_taxes 
GO 
-- Create the stored procedure. 
CREATE PROCEDURE proc_calculate_taxes (@p1 smallint = 42, @p2 char(1),  
      @p3 varchar(8) = 'CAR') 
    AS  
   SELECT *  
   FROM mytable 
 
      
     
    
proc_calculate_taxes 預存程序可以以多種組合方式執行： 
  

   

    

     
EXECUTE proc_calculate_taxes @p2 = 'A'

EXECUTE proc_calculate_taxes 69, 'B'

EXECUTE proc_calculate_taxes 69, 'C', 'House'

EXECUTE proc_calculate_taxes @p1 = DEFAULT, @p2 = 'D'

EXECUTE proc_calculate_taxes DEFAULT, @p3 = 'Local', @p2 = 'E'

EXECUTE proc_calculate_taxes 69, 'F', @p3 = DEFAULT

EXECUTE proc_calculate_taxes 95, 'G', DEFAULT

EXECUTE proc_calculate_taxes DEFAULT, 'H', DEFAULT

EXECUTE proc_calculate_taxes DEFAULT, 'I', @p3 = DEFAULT