發行項
04/02/2012

設計預存程序 (Database Engine)

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

設計預存程序的規則

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

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

CREATE AGGREGATE	CREATE RULE
CREATE DEFAULT	CREATE SCHEMA
CREATE 或 ALTER FUNCTION	CREATE or ALTER TRIGGER
CREATE 或 ALTER PROCEDURE	CREATE 或 ALTER VIEW
SET PARSEONLY	SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT	SET SHOWPLAN_XML
USE database_name

預存程序內可以建立其他資料庫物件。只要先行建立後，預存程序中就可以參考同一個預存程序所建立的物件。
預存程序中可以參考暫存資料表。
如果在預存程序中建立本機暫存資料表，該暫存資料表將只為該預存程序而存在；一旦結束預存程序，資料表就會消失。
如果執行的預存程序會呼叫別的預存程序，被呼叫的預存程序可以存取第一個預存程序所建立的所有物件，包括暫存資料表。
若執行了一個遠端預存程序而變更遠端的 Microsoft SQL Server 執行個體，則這些變更無法復原。交易不包含遠端預存程序。
預存程序中的參數個數上限是 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 2008 中，無法直接從任何系統資料表或系統檢視表中看見模糊化的輸出：對系統資料表、系統檢視表或資料庫檔案沒有存取權的使用者，將無法擷取模糊化的文字。但是，可直接存取資料庫檔案的授權使用者將可以取得這些文字。這些使用者可以對模糊化進行反向工程，以擷取預存程序定義的原始文字。

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