設計預存程序 (Database Engine)

任何可以寫成批次處理的 Transact-SQL 程式碼幾乎都可以用來建立預存程序。

設計預存程序的規則

設計預存程序的規則包括下列項目：

• CREATE PROCEDURE 定義本身可以包括任何數目與類型的 SQL 陳述式，除了下列陳述式以外。這些不能用在預存程序內的任何地方。

  CREATE AGGREGATE	CREATE RULE
  CREATE DEFAULT	CREATE SCHEMA
  CREATE 或 ALTER FUNCTION	CREATE 或 ALTER FUNCTION
  CREATE 或 ALTER PROCEDURE	CREATE 或 ALTER VIEW
  SET PARSEONLY	SET SHOWPLAN_ALL
  SET SHOWPLAN_TEXT	SET SHOWPLAN_XML
  使用 database_name。

• 預存程序內可以建立其他資料庫物件。只要先行建立後，預存程序中就可以參考同一個預存程序所建立的物件。

• 預存程序中可以參考暫存資料表。

• 如果在預存程序中建立本機暫存資料表，該暫存資料表將只為該預存程序而存在；一旦結束預存程序，資料表就會消失。

• 如果執行的預存程序會呼叫別的預存程序，被呼叫的預存程序可以存取第一個預存程序所建立的所有物件，包括暫存資料表。

• 若執行了一個遠端預存程序而變更遠端的 Microsoft SQL Server 2005 執行個體，則這些變更無法復原。交易不包含遠端預存程序。

• 預存程序中的參數個數上限是 2100 個。

• 只要有可用的記憶體，預存程序中的區域變數個數沒有限制。

• 依可用記憶體而定，預存程序的大小上限是 128 MB。

預存程序內的限定名稱

在預存程序內，若有非結構描述限定的陳述式 (例如 SELECT 或 INSERT)，它所使用的物件名稱會預設為預存程序的結構描述。建立預存程序的使用者如果不限定預存程序內 SELECT、INSERT、UPDATE 或 DELETE 陳述式所參考的資料表或檢視名稱，那麼在預設狀況下，只有程序的建立者可以透過預存程序存取那些資料表。

如果其他使用者要使用預存程序，所有資料定義語言 (DDL) 陳述式 (例如 CREATE、ALTER 或 DROP 陳述式、DBCC 陳述式、EXECUTE 和動態 SQL 陳述式) 所使用的物件名稱應該以物件的結構描述名稱限定。指定這些物件的結構描述名稱可確保名稱解析為相同的物件，無論是誰呼叫預存程序。如果未指定結構描述名稱，SQL Server 會嘗試使用 EXECUTE AS 子句中指定之呼叫者或使用者預設的結構描述名稱，然後再以 dbo 結構描述解析物件名稱。

模糊化程序定義

如果要模糊化 CREATE PROCEDURE 陳述式的原始文字，請使用 WITH ENCRYPTION 選項。在 SQL Server 2005 中，無法直接從任何系統資料表或檢視表中看見模糊化的輸出。對系統資料表、系統檢視表或資料庫檔案沒有存取權的使用者，無法擷取模糊化的文字。不過，可以直接存取資料庫檔案的特許使用者，則可以存取該文字。這些使用者可以利用還原工程還原模糊化的內容，以擷取預存程序定義的原始文字。

使用 WITH ENCRYPTION 選項可防止在 SQL Server 複寫中發行這個預存程序。

SET 陳述式選項

當 Transact-SQL 預存程序被建立或修改時，Database Engine 便會儲存 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 陳述式從預存程序被執行時，設定值在預存程序執行完畢前都會保持有效。然後設定值又會回復它在預存程序被呼叫前的值。這樣可以讓個別用戶端設定想要的選項，而不會影響到預存程序的邏輯。

附註：
在預存程序、使用者自訂函數中傳遞參數，或在批次陳述式中宣告和設定變數時，不會接受 ANSI_WARNINGS 的設定。例如，如果變數定義為 char(3)，然後設定為大於三個字元的值，資料會被截斷成定義的大小，而 INSERT 或 UPDATE 陳述式會成功。

請參閱

其他資源

瞭解預存程序
實作預存程序
CREATE PROCEDURE (Transact-SQL)

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄