SQL Server procedure

本文內容來自百度百科：http://baike.baidu.com/view/1031973.htm

CREATE PROCEDURE 建立預存程序，預存程序是儲存起來的可以接受和返回使用者提供的參數的 Transact-SQL 陳述式的集合。 可以建立一個過程供永久使用，或在一個會話中臨時使用（局部暫存處理序），或在所有會話中臨時使用（全域暫存處理序）。 也可以建立在 Microsoft SQL Server啟動時自動啟動並執行預存程序。

文法　　

      CREATE PROC [ EDURE ] procedure_name [ ; number ]

　　[ { @parameter data_type }

　　[ VARYING ] [ = default ] [ OUTPUT ]

　　] [ ,...n ]

　　[ WITH

　　{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

　　[ FOR REPLICATION ]

　　AS sql_statement [ ...n ]

參數

procedure_name

　　新預存程序的名稱。過程名必須符合標識符規則，且對於資料庫及其所有者必須唯一。有關更多資訊，請參見使用標識符。

　　要建立局部暫存處理序，可以在 procedure_name 前面加一個編號符 (#procedure_name)，要建立全域暫存處理序，可以在 procedure_name 前面加兩個編號符 (##procedure_name)。完整的名稱（包括 # 或 ##）不能超過 128 個字元。指定流程所有人的名稱是可選的。

number

　　是可選的整數，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應用程式使用的過程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標識符，則數字不應包含在標識符中，只應在 procedure_name 前後使用適當的定界符。

@parameter

　　過程中的參數。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數。使用者必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的預設值）。預存程序最多可以有 2.100 個參數。

　　使用 @ 符號作為第一個字元來指定參數名稱。參數名稱必須符合標識符的規則。每個過程的參數僅用於該過程本身；相同的參數名稱可以用在其它過程中。預設情況下，參數只能代替常量，而不能用於代替表名、列名或其它資料庫物件的名稱。有關更多資訊，請參見 EXECUTE。

data_type

　　參數的資料類型。所有資料類型（包括 text、ntext 和 image）均可以用作預存程序的參數。不過，cursor 資料類型只能用於 OUTPUT 參數。如果指定的資料類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關鍵字。有關 SQL Server 提供的資料類型及其文法的更多資訊，請參見資料類型。

　　說明 對於可以是 cursor 資料類型的輸出參數，沒有最大數目的限制。

VARYING

　　指定作為輸出參數支援的結果集（由預存程序動態構造，內容可以變化）。僅適用於遊標參數。

default

　　參數的預設值。如果定義了預設值，不必指定該參數的值即可執行過程。預設值必須是常量或 NULL。如果過程將對該參數使用 LIKE 關鍵字，那麼預設值中可以包含萬用字元（%、_、[] 和 [^]）。

OUTPUT

　　表明參數是返回參數。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數可將資訊返回給調用過程。Text、ntext 和 image 參數可用作 OUTPUT 參數。使用 OUTPUT 關鍵字的輸出參數可以是遊標預留位置。

n

　　表示最多可以指定 2.100 個參數的預留位置。

　　{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

　　RECOMPILE 表明 SQL Server 不會緩衝該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩衝在記憶體中的執行計畫時，請使用 RECOMPILE 選項。

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

　　說明 在升級過程中，SQL Server 利用儲存在 syscomments 中的加密注釋來重新建立加密過程。

FOR REPLICATION

　　指定不能在訂閱伺服器上執行為複製建立的預存程序。.使用 FOR REPLICATION 選項建立的預存程序可用作預存程序篩選，且只能在複製過程中執行。本選項不能和 WITH RECOMPILE 選項一起使用。

AS

　　指定過程要執行的操作。

sql_statement

　　過程中要包含的任意數目和類型的 Transact-SQL 陳述式。但有一些限制。

　　n

　　是表示此過程可以包含多條 Transact-SQL 陳述式的預留位置。

注釋　　

      預存程序的最大大小為 128 MB。

　　使用者定義的預存程序只能在當前資料庫中建立（暫存處理序除外，暫存處理序總是在 tempdb 中建立）。在單個批處理中，CREATE PROCEDURE 語句不能與其它 Transact-SQL 陳述式組合使用。

　　預設情況下，參數可為空白。為了防止向不允許使用 NULL 的列傳遞 NULL 參數值，應向過程中添加編程邏輯或為該列使用預設值（使用 CREATE 或 ALTER TABLE 的 DEFAULT 關鍵字)。

　　說明 SQL Server 是將Null 字元串解釋為單個空格還是解釋為真正的Null 字元串，由相容層級設定控制。如果相容層級小於或等於 65，SQL Server 就將Null 字元串解釋為單個空格。如果相容層級等於 70，則 SQL Server 將Null 字元串解釋為空白字串。有關更多資訊，請參見 sp_dbcmptlevel。

　　獲得有關預存程序的資訊

　　若要顯示用來建立過程的文本，請在過程所在的資料庫中執行 sp_helptext，並使用過程名作為參數。

　　說明 使用 ENCRYPTION 選項建立的預存程序不能使用 sp_helptext 查看。

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

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

引用對象　　

SQL Server 允許建立的預存程序引用尚不存在的對象。在建立時，只進行語法檢查。執行時，如果快取中尚無有效計劃，則編譯預存程序以產生執行計畫。只有在編譯過程中才解析預存程序中引用的所有對象。因此，如果文法正確的預存程序引用了不存在的對象，則仍可以成功建立，但在運行時將失敗，因為所引用的對象不存在。

執行預存程序　　

成功執行 CREATE PROCEDURE 語句後，過程名稱將儲存在 sysobjects 系統資料表中，而 CREATE PROCEDURE 語句的文本將儲存在 syscomments 中。第一次執行時，將編譯該過程以確定檢索資料的最佳訪問計劃。

　　使用 cursor 資料類型的參數

　　預存程序只能將 cursor 資料類型用於 OUTPUT 參數。如果為某個參數指定了 cursor 資料類型，也必須指定 VARYING 和 OUTPUT 參數。如果為某個參數指定了 VARYING 關鍵字，則資料類型必須是 cursor，並且必須指定 OUTPUT 關鍵字。