SQL Server預存程序的基礎說明

可以建立一個過程供永久使用，或在一個會話中臨時使用（局部暫存處理序），或在所有會話中臨時使用（全域暫存處理序）。
也可以建立在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 關鍵字的輸出參數可以是遊標預留位置。

表示最多可以指定 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 陳述式。但有一些限制。

是表示此過程可以包含多條 Transact-SQL 陳述式的預留位置。
注釋
預存程序的最大大小為 128 MB。
使用者定義的預存程序只能在當前資料庫中建立（暫存處理序除外，暫存處理序總是在 tempdb 中建立）。在單個批處理中，CREATE PROCEDURE 語句不能與其它 Transact-SQL 陳述式組合使用。
預設情況下，參數可為空白。如果傳遞 NULL 參數值並且該參數在 CREATE 或 ALTER TABLE 語句中使用，而該語句中引用的列又不允許使用 NULL，則 SQL Server 會產生一條錯誤資訊。為了防止向不允許使用 NULL 的列傳遞 NULL 參數值，應向過程中添加編程邏輯或為該列使用預設值（使用 CREATE 或 ALTER TABLE 的 DEFAULT 關鍵字)。
建議在預存程序的任何 CREATE TABLE 或 ALTER TABLE 語句中都為每列顯式指定 NULL 或 NOT NULL，例如在建立暫存資料表時。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 選項控制 SQL Server 為列指派 NULL 或 NOT NULL 特性的方式（如果在 CREATE TABLE 或 ALTER TABLE 語句中沒有指定的話）。如果某個串連執行的預存程序對這些選項的設定與建立該過程的串連的設定不同，則為第二個串連建立的表列可能會有不同的為空白性，並且表現出不同的行為方式。如果為每個列顯式聲明了 NULL 或 NOT NULL，那麼將對所有執行該預存程序的串連使用相同的為空白性建立暫存資料表。
在建立或更改預存程序時，SQL Server 將儲存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的設定。執行預存程序時，將使用這些原始設定。因此，所有用戶端工作階段的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 設定在執行預存程序時都將被忽略。在預存程序中出現的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 語句不影響預存程序的功能。
其它 SET 選項（例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS）在建立或更改預存程序時不儲存。如果預存程序的邏輯取決於特定的設定，應在過程開頭添加一條 SET 語句，以確保設定正確。從預存程序中執行 SET 語句時，該設定只在預存程序完成之前有效。之後，設定將恢複為調用預存程序時的值。這使個別的用戶端可以設定所需的選項，而不會影響預存程序的邏輯。
說明 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 允許建立的預存程序引用尚不存在的對象。在建立時，只進行語法檢查。執行時，如果快取中尚無有效計劃，則編譯預存程序以產生執行計畫。只有在編譯過程中才解析預存程序中引用的所有對象。因此，如果文法正確的預存程序引用了不存在的對象，則仍可以成功建立，但在運行時將失敗，因為所引用的對象不存在。有關更多資訊，請參見延遲名稱解析和編譯。
延遲名稱解析和相容層級
SQL Server 允許 Transact-SQL 預存程序在建立時引用不存在的表。這種能力稱為延遲名稱解析。不過，如果 Transact-SQL 預存程序引用了該預存程序中定義的表，而相容層級設定（通過執行 sp_dbcmptlevel 來設定）為 65，則在建立時會發出警告資訊。而如果在運行時所引用的表不存在，將返回錯誤資訊。有關更多資訊，請參見 sp_dbcmptlevel 和延遲名稱解析和編譯。
執行預存程序
成功執行 CREATE PROCEDURE 語句後，過程名稱將儲存在 sysobjects 系統資料表中，而 CREATE PROCEDURE 語句的文本將儲存在 syscomments 中。第一次執行時，將編譯該過程以確定檢索資料的最佳訪問計劃。
使用 cursor 資料類型的參數
預存程序只能將 cursor 資料類型用於 OUTPUT 參數。如果為某個參數指定了 cursor 資料類型，也必須指定 VARYING 和 OUTPUT 參數。如果為某個參數指定了 VARYING 關鍵字，則資料類型必須是 cursor，並且必須指定 OUTPUT 關鍵字。