存儲過程可以使得對數(shù)據(jù)庫的管理、以及顯示關(guān)于數(shù)據(jù)庫及其用戶信息的工作容易得多。存儲過程是 SQL 語句和可選控制流語句的預(yù)編譯集合，以一個名稱存儲并作為一個單元處理。存儲過程存儲在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序通過一個調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其它強(qiáng)大的編程功能。

存儲過程可包含程序流、邏輯以及對數(shù)據(jù)庫的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個或多個結(jié)果集以及返回值。

可以出于任何使用 SQL 語句的目的來使用存儲過程，它具有以下優(yōu)點：

• 可以在單個存儲過程中執(zhí)行一系列 SQL 語句。
  
• 可以從自己的存儲過程內(nèi)引用其它存儲過程，這可以簡化一系列復(fù)雜語句。
  
• 存儲過程在創(chuàng)建時即在服務(wù)器上進(jìn)行編譯，所以執(zhí)行起來比單個 SQL 語句快。

存儲過程的功能取決于數(shù)據(jù)庫所提供的功能。

創(chuàng)建存儲過程

可使用 Transact-SQL 語句 CREATE PROCEDURE 創(chuàng)建存儲過程。創(chuàng)建存儲過程前，請考慮下列事項：

• 不能將 CREATE PROCEDURE 語句與其它 SQL 語句組合到單個批處理中。
  
• 創(chuàng)建存儲過程的權(quán)限默認(rèn)屬于數(shù)據(jù)庫所有者，該所有者可將此權(quán)限授予其他用戶。
  
• 存儲過程是數(shù)據(jù)庫對象，其名稱必須遵守標(biāo)識符規(guī)則。
  
• 只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲過程。

創(chuàng)建存儲過程時，應(yīng)指定：

• 所有輸入?yún)?shù)和向調(diào)用過程或批處理返回的輸出參數(shù)。
  
• 執(zhí)行數(shù)據(jù)庫操作（包括調(diào)用其它過程）的編程語句。
  
• 返回至調(diào)用過程或批處理以表明成功或失?。ㄒ约笆≡颍┑臓顟B(tài)值。

系統(tǒng)存儲過程

Microsoft? SQL Server? 2000 中的許多管理活動是通過一種稱為系統(tǒng)存儲過程的特殊過程執(zhí)行的。系統(tǒng)存儲過程在 master 數(shù)據(jù)庫中創(chuàng)建并存儲，帶有 sp_ 前綴?？蓮娜魏螖?shù)據(jù)庫中執(zhí)行系統(tǒng)存儲過程，而無需使用 master 數(shù)據(jù)庫名稱來完全限定該存儲過程的名稱。

強(qiáng)烈建議您不要創(chuàng)建以 sp_ 為前綴的存儲過程。SQL Server 始終按照下列順序查找以 sp_ 開頭的存儲過程：

1. 在 master 數(shù)據(jù)庫中查找存儲過程。
   
2. 根據(jù)所提供的任何限定符（數(shù)據(jù)庫名稱或所有者）查找該存儲過程。
   
3. 如果未指定所有者，則使用 dbo 作為所有者查找該存儲過程。

因此，雖然當(dāng)前數(shù)據(jù)庫中可能存在帶 sp_ 前綴的用戶創(chuàng)建的存儲過程，但總會先檢查 master 數(shù)據(jù)庫（即使該存儲過程已用數(shù)據(jù)庫名稱限定）。

 重要  如果用戶創(chuàng)建的存儲過程與系統(tǒng)存儲過程同名，則永遠(yuǎn)不執(zhí)行用戶創(chuàng)建的存儲過程。

分組

如果將一個不同的標(biāo)識號賦予某過程，則可以用與現(xiàn)有某存儲過程相同的名稱創(chuàng)建該過程，這樣可允許將這些過程進(jìn)行邏輯分組。同名的分組過程可以同時刪除。在同一應(yīng)用程序中使用的過程一般都以該方式分組。例如，用于 my_app 應(yīng)用程序的過程可能被命名為 my_proc;1、my_proc;2 等。刪除 my_proc 即刪除該整個組。將過程分組后，就無法刪除該組內(nèi)的單個過程。

臨時存儲過程

專用和全局臨時存儲過程與臨時表類似，都可以用向該過程名稱添加 # 和 ## 前綴的方法進(jìn)行創(chuàng)建。# 表示本地臨時存儲過程，## 表示全局臨時存儲過程。SQL Server 關(guān)閉后，這些過程將不再存在。

臨時存儲過程在連接到 SQL Server 的早期版本時很有用，這些早期版本不支持再次使用 Transact-SQL 語句或批處理執(zhí)行計劃。連接到 SQL Server 2000 的應(yīng)用程序應(yīng)使用 sp_executesql 系統(tǒng)存儲過程，而不使用臨時存儲過程。有關(guān)更多信息，請參見執(zhí)行計劃的高速緩存和重新使用。

只有創(chuàng)建本地臨時過程的連接才能執(zhí)行該過程，當(dāng)該連接關(guān)閉（用戶從 SQL Server 中注銷）時，將自動刪除該過程。

任何連接都可執(zhí)行全局臨時存儲過程。只有創(chuàng)建該過程的用戶所用的連接關(guān)閉，并且所有其它連接所用的該過程的當(dāng)前執(zhí)行版本運(yùn)行完畢后，全局臨時存儲過程才不再存在。一旦用于創(chuàng)建該過程的連接關(guān)閉，將不再允許啟動執(zhí)行該全局臨時存儲過程。只允許那些已啟動執(zhí)行該存儲過程的連接完成該過程的運(yùn)行。

如果直接在 tempdb 數(shù)據(jù)庫中創(chuàng)建沒有 # 或 ## 前綴的存儲過程，則由于每次啟動 SQL Server 時 tempdb 都要重新創(chuàng)建，因此當(dāng)關(guān)閉 SQL Server 時將自動刪除該存儲過程。直接在 tempdb 中創(chuàng)建的過程即使在創(chuàng)建該過程的連接終止后也會存在。與任何其它對象一樣，可向其他用戶授予、拒絕和廢除執(zhí)行該臨時存儲過程的權(quán)限。

CREATE PROCEDURE

創(chuàng)建存儲過程，存儲過程是保存起來的可以接受和返回用戶提供的參數(shù)的 Transact-SQL 語句的集合。

可以創(chuàng)建一個過程供永久使用，或在一個會話中臨時使用（局部臨時過程），或在所有會話中臨時使用（全局臨時過程）。

也可以創(chuàng)建在 Microsoft? SQL Server? 啟動時自動運(yùn)行的存儲過程。

語法

CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]

[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

參數(shù)

procedure_name

新存儲過程的名稱。過程名必須符合標(biāo)識符規(guī)則，且對于數(shù)據(jù)庫及其所有者必須唯一。有關(guān)更多信息，請參見使用標(biāo)識符。

要創(chuàng)建局部臨時過程，可以在 procedure_name 前面加一個編號符 (#procedure_name)，要創(chuàng)建全局臨時過程，可以在 procedure_name 前面加兩個編號符 (##procedure_name)。完整的名稱（包括 # 或 ##）不能超過 128 個字符。指定過程所有者的名稱是可選的。

;number

是可選的整數(shù)，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應(yīng)用程序使用的過程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標(biāo)識符，則數(shù)字不應(yīng)包含在標(biāo)識符中，只應(yīng)在 procedure_name 前后使用適當(dāng)?shù)亩ń绶?/P>

@parameter

過程中的參數(shù)。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數(shù)。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。存儲過程最多可以有 2.100 個參數(shù)。

使用 @ 符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以用在其它過程中。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。有關(guān)更多信息，請參見 EXECUTE。

data_type

參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括 text、ntext 和 image）均可以用作存儲過程的參數(shù)。不過，cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定的數(shù)據(jù)類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關(guān)鍵字。有關(guān) SQL Server 提供的數(shù)據(jù)類型及其語法的更多信息，請參見數(shù)據(jù)類型。

說明  對于可以是 cursor 數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限制。

VARYING

指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。

default

參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或 NULL。如果過程將對該參數(shù)使用 LIKE 關(guān)鍵字，那么默認(rèn)值中可以包含通配符（%、_、[] 和 [^]）。

OUTPUT

表明參數(shù)是返回參數(shù)。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過程。Text、ntext 和 image 參數(shù)可用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符。

n

表示最多可以指定 2.100 個參數(shù)的占位符。

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE 表明 SQL Server 不會緩存該過程的計劃，該過程將在運(yùn)行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計劃時，請使用 RECOMPILE 選項。

ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復(fù)制的一部分發(fā)布。

說明  在升級過程中，SQL Server 利用存儲在 syscomments 中的加密注釋來重新創(chuàng)建加密過程。

FOR REPLICATION

指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程。.使用 FOR REPLICATION 選項創(chuàng)建的存儲過程可用作存儲過程篩選，且只能在復(fù)制過程中執(zhí)行。本選項不能和 WITH RECOMPILE 選項一起使用。

AS

指定過程要執(zhí)行的操作。

sql_statement

過程中要包含的任意數(shù)目和類型的 Transact-SQL 語句。但有一些限制。

n

是表示此過程可以包含多條 Transact-SQL 語句的占位符。

注釋

存儲過程的最大大小為 128 MB。

用戶定義的存儲過程只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建（臨時過程除外，臨時過程總是在 tempdb 中創(chuàng)建）。在單個批處理中，CREATE PROCEDURE 語句不能與其它 Transact-SQL 語句組合使用。

默認(rèn)情況下，參數(shù)可為空。如果傳遞 NULL 參數(shù)值并且該參數(shù)在 CREATE 或 ALTER TABLE 語句中使用，而該語句中引用的列又不允許使用 NULL，則 SQL Server 會產(chǎn)生一條錯誤信息。為了防止向不允許使用 NULL 的列傳遞 NULL 參數(shù)值，應(yīng)向過程中添加編程邏輯或為該列使用默認(rèn)值（使用 CREATE 或 ALTER TABLE 的 DEFAULT 關(guān)鍵字)。

建議在存儲過程的任何 CREATE TABLE 或 ALTER TABLE 語句中都為每列顯式指定 NULL 或 NOT NULL，例如在創(chuàng)建臨時表時。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 選項控制 SQL Server 為列指派 NULL 或 NOT NULL 特性的方式（如果在 CREATE TABLE 或 ALTER TABLE 語句中沒有指定的話）。如果某個連接執(zhí)行的存儲過程對這些選項的設(shè)置與創(chuàng)建該過程的連接的設(shè)置不同，則為第二個連接創(chuàng)建的表列可能會有不同的為空性，并且表現(xiàn)出不同的行為方式。如果為每個列顯式聲明了 NULL 或 NOT NULL，那么將對所有執(zhí)行該存儲過程的連接使用相同的為空性創(chuàng)建臨時表。

在創(chuàng)建或更改存儲過程時，SQL Server 將保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的設(shè)置。執(zhí)行存儲過程時，將使用這些原始設(shè)置。因此，所有客戶端會話的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 設(shè)置在執(zhí)行存儲過程時都將被忽略。在存儲過程中出現(xiàn)的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 語句不影響存儲過程的功能。

其它 SET 選項（例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS）在創(chuàng)建或更改存儲過程時不保存。如果存儲過程的邏輯取決于特定的設(shè)置，應(yīng)在過程開頭添加一條 SET 語句，以確保設(shè)置正確。從存儲過程中執(zhí)行 SET 語句時，該設(shè)置只在存儲過程完成之前有效。之后，設(shè)置將恢復(fù)為調(diào)用存儲過程時的值。這使個別的客戶端可以設(shè)置所需的選項，而不會影響存儲過程的邏輯。

說明  SQL Server 是將空字符串解釋為單個空格還是解釋為真正的空字符串，由兼容級別設(shè)置控制。如果兼容級別小于或等于 65，SQL Server 就將空字符串解釋為單個空格。如果兼容級別等于 70，則 SQL Server 將空字符串解釋為空字符串。有關(guān)更多信息，請參見 sp_dbcmptlevel。

獲得有關(guān)存儲過程的信息

若要顯示用來創(chuàng)建過程的文本，請在過程所在的數(shù)據(jù)庫中執(zhí)行 sp_helptext，并使用過程名作為參數(shù)。

說明  使用 ENCRYPTION 選項創(chuàng)建的存儲過程不能使用 sp_helptext 查看。

若要顯示有關(guān)過程引用的對象的報表，請使用 sp_depends。

若要為過程重命名，請使用 sp_rename。

引用對象

SQL Server 允許創(chuàng)建的存儲過程引用尚不存在的對象。在創(chuàng)建時，只進(jìn)行語法檢查。執(zhí)行時，如果高速緩存中尚無有效的計劃，則編譯存儲過程以生成執(zhí)行計劃。只有在編譯過程中才解析存儲過程中引用的所有對象。因此，如果語法正確的存儲過程引用了不存在的對象，則仍可以成功創(chuàng)建，但在運(yùn)行時將失敗，因為所引用的對象不存在。有關(guān)更多信息，請參見延遲名稱解析和編譯。

延遲名稱解析和兼容級別

SQL Server 允許 Transact-SQL 存儲過程在創(chuàng)建時引用不存在的表。這種能力稱為延遲名稱解析。不過，如果 Transact-SQL 存儲過程引用了該存儲過程中定義的表，而兼容級別設(shè)置（通過執(zhí)行 sp_dbcmptlevel 來設(shè)置）為 65，則在創(chuàng)建時會發(fā)出警告信息。而如果在運(yùn)行時所引用的表不存在，將返回錯誤信息。有關(guān)更多信息，請參見 sp_dbcmptlevel 和延遲名稱解析和編譯。

執(zhí)行存儲過程

成功執(zhí)行 CREATE PROCEDURE 語句后，過程名稱將存儲在 sysobjects 系統(tǒng)表中，而 CREATE PROCEDURE 語句的文本將存儲在 syscomments 中。第一次執(zhí)行時，將編譯該過程以確定檢索數(shù)據(jù)的最佳訪問計劃。

使用 cursor 數(shù)據(jù)類型的參數(shù)

存儲過程只能將 cursor 數(shù)據(jù)類型用于 OUTPUT 參數(shù)。如果為某個參數(shù)指定了 cursor 數(shù)據(jù)類型，也必須指定 VARYING 和 OUTPUT 參數(shù)。如果為某個參數(shù)指定了 VARYING 關(guān)鍵字，則數(shù)據(jù)類型必須是 cursor，并且必須指定 OUTPUT 關(guān)鍵字。

說明  cursor 數(shù)據(jù)類型不能通過數(shù)據(jù)庫 API（例如 OLE DB、ODBC、ADO 和 DB-Library）綁定到應(yīng)用程序變量上。因為必須先綁定 OUTPUT 參數(shù)，應(yīng)用程序才可以執(zhí)行存儲過程，所以帶有 cursor OUTPUT 參數(shù)的存儲過程不能通過數(shù)據(jù)庫 API 調(diào)用。只有將 cursor OUTPUT 變量賦值給 Transact-SQL 局部 cursor 變量時，才可以通過 Transact-SQL 批處理、存儲過程或觸發(fā)器調(diào)用這些過程。

Cursor 輸出參數(shù)

在執(zhí)行過程時，以下規(guī)則適用于 cursor 輸出參數(shù)：

1. 對于只進(jìn)游標(biāo)，游標(biāo)的結(jié)果集中返回的行只是那些存儲過程執(zhí)行結(jié)束時處于或超出游標(biāo)位置的行，例如：
   1. 在過程中的名為 RS 的 100 行結(jié)果集上打開一個非滾動游標(biāo)。
      
      
   2. 過程提取結(jié)果集 RS 的頭 5 行。
      
      
   3. 過程返回到其調(diào)用者。
      
      
   4. 返回到調(diào)用者的結(jié)果集 RS 由 RS 的第 6 到 100 行組成，調(diào)用者中的游標(biāo)處于 RS 的第一行之前。
2. 對于只進(jìn)游標(biāo)，如果存儲過程完成后，游標(biāo)位于第一行的前面，則整個結(jié)果集將返回給調(diào)用批處理、存儲過程或觸發(fā)器。返回時，游標(biāo)將位于第一行的前面。
   
   
3. 對于只進(jìn)游標(biāo)，如果存儲過程完成后，游標(biāo)的位置超出最后一行的結(jié)尾，則為調(diào)用批處理、存儲過程或觸發(fā)器返回空結(jié)果集。
   

   說明  空結(jié)果集與空值不同。

4. 對于可滾動游標(biāo)，在存儲過程執(zhí)行結(jié)束時，結(jié)果集中的所有行均會返回給調(diào)用批處理、存儲過程或觸發(fā)器。返回時，游標(biāo)保留在過程中最后一次執(zhí)行提取時的位置。
   
   
5. 對于任意類型的游標(biāo)，如果游標(biāo)關(guān)閉，則將空值傳遞回調(diào)用批處理、存儲過程或觸發(fā)器。如果將游標(biāo)指派給一個參數(shù)，但該游標(biāo)從未打開過，也會出現(xiàn)這種情況。

說明  關(guān)閉狀態(tài)只有在返回時才有影響。例如，可以在過程中關(guān)閉游標(biāo)，稍后再打開游標(biāo)，然后將該游標(biāo)的結(jié)果集返回給調(diào)用批處理、存儲過程或觸發(fā)器。

臨時存儲過程

SQL Server 支持兩種臨時過程：局部臨時過程和全局臨時過程。局部臨時過程只能由創(chuàng)建該過程的連接使用。全局臨時過程則可由所有連接使用。局部臨時過程在當(dāng)前會話結(jié)束時自動除去。全局臨時過程在使用該過程的最后一個會話結(jié)束時除去。通常是在創(chuàng)建該過程的會話結(jié)束時。

臨時過程用 # 和 ## 命名，可以由任何用戶創(chuàng)建。創(chuàng)建過程后，局部過程的所有者是唯一可以使用該過程的用戶。執(zhí)行局部臨時過程的權(quán)限不能授予其他用戶。如果創(chuàng)建了全局臨時過程，則所有用戶均可以訪問該過程，權(quán)限不能顯式廢除。只有在 tempdb 數(shù)據(jù)庫中具有顯式 CREATE PROCEDURE 權(quán)限的用戶，才可以在該數(shù)據(jù)庫中顯式創(chuàng)建臨時過程（不使用編號符命名）?？梢允谟杌驈U除這些過程中的權(quán)限。

說明  頻繁使用臨時存儲過程會在 tempdb 中的系統(tǒng)表上產(chǎn)生爭用，從而對性能產(chǎn)生負(fù)面影響。建議使用 sp_executesql 代替。sp_executesql 不在系統(tǒng)表中存儲數(shù)據(jù)，因此可以避免這一問題。

自動執(zhí)行存儲過程

SQL Server 啟動時可以自動執(zhí)行一個或多個存儲過程。這些存儲過程必須由系統(tǒng)管理員創(chuàng)建，并在 sysadmin 固定服務(wù)器角色下作為后臺過程執(zhí)行。這些過程不能有任何輸入?yún)?shù)。

對啟動過程的數(shù)目沒有限制，但是要注意，每個啟動過程在執(zhí)行時都會占用一個連接。如果必須在啟動時執(zhí)行多個過程，但不需要并行執(zhí)行，則可以指定一個過程作為啟動過程，讓該過程調(diào)用其它過程。這樣就只占用一個連接。

在啟動時恢復(fù)了最后一個數(shù)據(jù)庫后，即開始執(zhí)行存儲過程。若要跳過這些存儲過程的執(zhí)行，請將啟動參數(shù)指定為跟蹤標(biāo)記 4022。如果以最低配置啟動 SQL Server（使用 -f 標(biāo)記），則啟動存儲過程也不會執(zhí)行。有關(guān)更多信息，請參見跟蹤標(biāo)記。

若要創(chuàng)建啟動存儲過程，必須作為 sysadmin 固定服務(wù)器角色的成員登錄，并在 master 數(shù)據(jù)庫中創(chuàng)建存儲過程。

使用 sp_procoption 可以：

1. 將現(xiàn)有存儲過程指定為啟動過程。
   
   
2. 停止在 SQL Server 啟動時執(zhí)行過程。
   
   
3. 查看 SQL Server 啟動時執(zhí)行的所有過程的列表。

存儲過程嵌套

存儲過程可以嵌套，即一個存儲過程可以調(diào)用另一個存儲過程。在被調(diào)用過程開始執(zhí)行時，嵌套級將增加，在被調(diào)用過程執(zhí)行結(jié)束后，嵌套級將減少。如果超出最大的嵌套級，會使整個調(diào)用過程鏈?zhǔn)　？捎?@@NESTLEVEL 函數(shù)返回當(dāng)前的嵌套級。

若要估計編譯后的存儲過程大小，請使用下列性能監(jiān)視計數(shù)器。

性能監(jiān)視器對象名	性能監(jiān)視計數(shù)器名稱
SQLServer：緩沖區(qū)管理器	高速緩存大小（頁面數(shù)）
SQLServer：高速緩存管理器	高速緩存命中率
	高速緩存頁
	高速緩存對象計數(shù)*

* 各種分類的高速緩存對象均可以使用這些計數(shù)器，包括特殊 sql、準(zhǔn)備 sql、過程、觸發(fā)器等。

有關(guān)更多信息，請參見 SQL Server:Buffer Manager 對象和 SQL Server:Cache Manager 對象。

sql_statement 限制

除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 之外（這兩個語句必須是批處理中僅有的語句），任何 SET 語句均可以在存儲過程內(nèi)部指定。所選擇的 SET 選項在存儲過程執(zhí)行過程中有效，之后恢復(fù)為原來的設(shè)置。

如果其他用戶要使用某個存儲過程，那么在該存儲過程內(nèi)部，一些語句使用的對象名必須使用對象所有者的名稱限定。這些語句包括：

1. ALTER TABLE
   
2. CREATE INDEX
   
3. CREATE TABLE
   
4. 所有 DBCC 語句
   
5. DROP TABLE
   
6. DROP INDEX
   
7. TRUNCATE TABLE
   
8. UPDATE STATISTICS

權(quán)限

CREATE PROCEDURE 的權(quán)限默認(rèn)授予 sysadmin 固定服務(wù)器角色成員和 db_owner 和 db_ddladmin 固定數(shù)據(jù)庫角色成員。sysadmin 固定服務(wù)器角色成員和 db_owner 固定數(shù)據(jù)庫角色成員可以將 CREATE PROCEDURE 權(quán)限轉(zhuǎn)讓給其他用戶。執(zhí)行存儲過程的權(quán)限授予過程的所有者，該所有者可以為其它數(shù)據(jù)庫用戶設(shè)置執(zhí)行權(quán)限。

示例

A. 使用帶有復(fù)雜 SELECT 語句的簡單過程

下面的存儲過程從四個表的聯(lián)接中返回所有作者（提供了姓名）、出版的書籍以及出版社。該存儲過程不使用任何參數(shù)。

USE pubs
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'au_info_all' AND type = 'P')
   DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
   FROM authors a INNER JOIN titleauthor ta
      ON a.au_id = ta.au_id INNER JOIN titles t
      ON t.title_id = ta.title_id INNER JOIN publishers p
      ON t.pub_id = p.pub_id
GO

au_info_all 存儲過程可以通過以下方法執(zhí)行：

EXECUTE au_info_all
-- Or
EXEC au_info_all

如果該過程是批處理中的第一條語句，則可使用：

au_info_all

B. 使用帶有參數(shù)的簡單過程

下面的存儲過程從四個表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該存儲過程接受與傳遞的參數(shù)精確匹配的值。

USE pubs
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'au_info' AND type = 'P')
   DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info 
   @lastname varchar(40), 
   @firstname varchar(20) 
AS 
SELECT au_lname, au_fname, title, pub_name
   FROM authors a INNER JOIN titleauthor ta
      ON a.au_id = ta.au_id INNER JOIN titles t
      ON t.title_id = ta.title_id INNER JOIN publishers p
      ON t.pub_id = p.pub_id
   WHERE  au_fname = @firstname
      AND au_lname = @lastname
GO

au_info 存儲過程可以通過以下方法執(zhí)行：

EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- Or
EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

如果該過程是批處理中的第一條語句，則可使用：

au_info 'Dull', 'Ann'
-- Or
au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
au_info @firstname = 'Ann', @lastname = 'Dull'

C. 使用帶有通配符參數(shù)的簡單過程

下面的存儲過程從四個表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該存儲過程對傳遞的參數(shù)進(jìn)行模式匹配，如果沒有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值。

USE pubs
IF EXISTS (SELECT name FROM sysobjects 
      WHERE name = 'au_info2' AND type = 'P')
   DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
   @lastname varchar(30) = 'D%',
   @firstname varchar(18) = '%'
AS 
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
   ON a.au_id = ta.au_id INNER JOIN titles t
   ON t.title_id = ta.title_id INNER JOIN publishers p
   ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
   AND au_lname LIKE @lastname
GO

au_info2 存儲過程可以用多種組合執(zhí)行。下面只列出了部分組合：

EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'
-- Or
EXECUTE au_info2 @firstname = 'A%'
-- Or
EXECUTE au_info2 '[CK]ars[OE]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'

D. 使用 OUTPUT 參數(shù)

OUTPUT 參數(shù)允許外部過程、批處理或多條 Transact-SQL 語句訪問在過程執(zhí)行期間設(shè)置的某個值。下面的示例創(chuàng)建一個存儲過程 (titles_sum)，并使用一個可選的輸入?yún)?shù)和一個輸出參數(shù)。

首先，創(chuàng)建過程：

USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
      WHERE name = 'titles_sum' AND type = 'P')
   DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles 
WHERE title LIKE @@TITLE 
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO

接下來，將該 OUTPUT 參數(shù)用于控制流語言。

說明  OUTPUT 變量必須在創(chuàng)建表和使用該變量時都進(jìn)行定義。

參數(shù)名和變量名不一定要匹配，不過數(shù)據(jù)類型和參數(shù)位置必須匹配（除非使用 @@SUM = variable 形式）。

DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200 
BEGIN
   PRINT ' '
   PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
   SELECT 'The total cost of these titles is $' 
         + RTRIM(CAST(@@TOTALCOST AS varchar(20)))

下面是結(jié)果集：

Title Name                                                               
------------------------------------------------------------------------ 
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking

(3 row(s) affected)

Warning, null value eliminated from aggregate.
 
All of these titles can be purchased for less than $200.

E. 使用 OUTPUT 游標(biāo)參數(shù)

OUTPUT 游標(biāo)參數(shù)用來將存儲過程的局部游標(biāo)傳遞回調(diào)用批處理、存儲過程或觸發(fā)器。

首先，創(chuàng)建以下過程，在 titles 表上聲明并打開一個游標(biāo)：

USE pubs
IF EXISTS (SELECT name FROM sysobjects 
      WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles

OPEN @titles_cursor
GO

接下來，執(zhí)行一個批處理，聲明一個局部游標(biāo)變量，執(zhí)行上述過程以將游標(biāo)賦值給局部變量，然后從該游標(biāo)提取行。

USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
   FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

F. 使用 WITH RECOMPILE 選項

如果為過程提供的參數(shù)不是典型的參數(shù)，并且新的執(zhí)行計劃不應(yīng)高速緩存或存儲在內(nèi)存中，WITH RECOMPILE 子句會很有幫助。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'titles_by_author' AND type = 'P')
   DROP PROCEDURE titles_by_author
GO
CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '%'
WITH RECOMPILE
AS
SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name',
   title AS Title
FROM authors a INNER JOIN titleauthor ta 
   ON a.au_id = ta.au_id INNER JOIN titles t
   ON ta.title_id = t.title_id
WHERE au_lname LIKE @@LNAME_PATTERN
GO

G. 使用 WITH ENCRYPTION 選項

WITH ENCRYPTION 子句對用戶隱藏存儲過程的文本。下例創(chuàng)建加密過程，使用 sp_helptext 系統(tǒng)存儲過程獲取關(guān)于加密過程的信息，然后嘗試直接從 syscomments 表中獲取關(guān)于該過程的信息。

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'encrypt_this' AND type = 'P')
   DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT * 
FROM authors
GO

EXEC sp_helptext encrypt_this

下面是結(jié)果集：

The object's comments have been encrypted.

接下來，選擇加密存儲過程內(nèi)容的標(biāo)識號和文本。

SELECT c.id, c.text 
FROM syscomments c INNER JOIN sysobjects o
   ON c.id = o.id
WHERE o.name = 'encrypt_this'

下面是結(jié)果集：

說明  text 列的輸出顯示在單獨一行中。執(zhí)行時，該信息將與 id 列信息出現(xiàn)在同一行中。

id         text                                                        
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e?????????????????????????????????????????

(1 row(s) affected)

H. 創(chuàng)建用戶定義的系統(tǒng)存儲過程

下面的示例創(chuàng)建一個過程，顯示表名以 emp 開頭的所有表及其對應(yīng)的索引。如果沒有指定參數(shù)，該過程將返回表名以 sys 開頭的所有表（及索引）。

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'sp_showindexes' AND type = 'P')
   DROP PROCEDURE sp_showindexes
GO
USE master
GO
CREATE PROCEDURE sp_showindexes
   @@TABLE varchar(30) = 'sys%'
AS 
SELECT o.name AS TABLE_NAME,
   i.name AS INDEX_NAME, 
   indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects o
   ON o.id = i.id 
WHERE o.name LIKE @@TABLE
GO         
USE pubs
EXEC sp_showindexes 'emp%'
GO

下面是結(jié)果集：

TABLE_NAME       INDEX_NAME       INDEX_ID 
---------------- ---------------- ----------------
employee         employee_ind     1
employee         PK_emp_id        2

(2 row(s) affected)

I. 使用延遲名稱解析

下面的示例顯示四個過程以及延遲名稱解析的各種可能使用方式。盡管引用的表或列在編譯時不存在，但每個存儲過程都可創(chuàng)建。

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'proc1' AND type = 'P')
   DROP PROCEDURE proc1
GO
-- Creating a procedure on a nonexistent table.
USE pubs
GO
CREATE PROCEDURE proc1
AS
   SELECT *
   FROM does_not_exist
GO  
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c 
   ON o.id = c.id
WHERE o.type = 'P' AND o.name = 'proc1'
GO
USE master
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'proc2' AND type = 'P')
   DROP PROCEDURE proc2
GO
-- Creating a procedure that attempts to retrieve information from a
-- nonexistent column in an existing table.
USE pubs
GO
CREATE PROCEDURE proc2
AS
   DECLARE @middle_init char(1)
   SET @middle_init = NULL
   SELECT au_id, middle_initial = @middle_init
   FROM authors
GO  
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c 
   ON o.id = c.id
WHERE o.type = 'P' and o.name = 'proc2'
轉(zhuǎn)自: http://goaler.xicp.net/ShowLog.asp?ID=515