CREATE PROCEDURE (Transact-SQL)
建立預存程序。預存程序是已儲存的 Transact-SQL 陳述式集合，或可取用及傳回使用者提供之參數的 Microsoft .NET Framework Common Language Runtime (CLR) 方法的參考。程序建立後可作為永久使用或在工作階段內作為暫時使用 (本機暫存程序)，或者，可在所有工作階段內作為暫時使用 (全域暫存程序)。
預存程序建立後也可在 SQL Server 的執行個體啟動時自動執行。
.gif "主題連結圖示")
語法
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }
<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name
引數
schema_name
這是程序所屬的結構描述名稱。procedure_name
這是新預存程序的名稱。程序名稱必須符合識別碼的規則，且在結構描述內必須是唯一的。強烈建議您不要在程序名稱中使用前置詞 sp_。這個前置詞是供 SQL Server 指定系統預存程序時使用。如需詳細資訊，請參閱＜建立預存程序 (Database Engine)＞。
針對本機暫存程序，可以在 procedure_name 前面利用一個數字符號 (#) 來建立本機暫存程序 (#procedure_name)；針對全域暫存程序，可以在前面加上兩個數字符號來建立全域暫存程序 (##procedure_name)。無法為 CLR 預存程序指定暫存名稱。
預存程序或全域暫存預存程序的完整名稱 (包括 ##) 不能超過 128 個字元。本機暫存預存程序的完整名稱 (包括 #) 不能超過 116 個字元。
**;**number
這是用來將同名程序分組的選擇性整數。您可以利用一個 DROP PROCEDURE 陳述式一併卸除這些分組的程序。例如，一個稱為 orders 的應用程式可能使用名為 orderproc;1、orderproc;2 等等的程序。DROP PROCEDURE orderproc 陳述式可以卸除整個群組。如果名稱包含分隔識別碼，則不應該併入號碼做為識別碼的一部分；請只在 procedure_name 前後使用適當的分隔符號。編號預存程序有下列限制：
不能使用 xml 或 CLR 使用者定義型別做為資料類型。
不能在編號的預存程序上建立計畫指南。
[!附註]
未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。
**@**parameter
這是程序中的參數。您可以在 CREATE PROCEDURE 陳述式中宣告一個或多個參數。除非定義了參數的預設值或將值設為等於其他參數，否則，在呼叫程序時，使用者必須提供每個已宣告參數的值。預存程序最多可以有 2,100 個參數。如果某個程序包含資料表值參數，而且此參數在呼叫中遺漏，就會傳入空白資料表的預設值。使用 @ 記號 (@) 當做第一個字元來指定參數名稱。參數名稱必須符合識別碼的規則。對程序而言，參數必須是本機參數；相同的參數名稱可以用在其他程序中。依預設，參數只能取代常數運算式，不能用來取代資料表名稱、資料行名稱或其他資料庫物件的名稱。如需詳細資訊，請參閱＜EXECUTE (Transact-SQL)＞。
如果指定了 FOR REPLICATION，就不能宣告參數。
[ type_schema_name**.** ] data_type
這是參數資料類型及其所屬的結構描述。所有資料類型都可以當做 Transact-SQL 預存程序的參數。您可以使用使用者定義資料表類型，將資料表值參數宣告為 Transact-SQL 預存程序的參數。資料表值參數只能指定為輸入參數，而且必須與 READONLY 關鍵字一起使用。cursor 資料類型只能用在 OUTPUT 參數。當您指定 cursor 資料類型時，您也必須指定 VARYING 和 OUTPUT 關鍵字。您可以搭配 cursor 資料類型，指定多個輸出參數。如果是 CLR 預存程序，不能指定 char、varchar、text、ntext、image、cursor、使用者定義資料表類型和 table 做為參數。如需有關 CLR 類型與 SQL Server 系統資料類型之間對應的詳細資訊，請參閱＜對應 CLR 參數資料＞。如需有關 SQL Server 系統資料類型及其語法的詳細資訊，請參閱＜資料類型 (Transact-SQL)＞。
如果參數的資料類型是 CLR 使用者定義型別，您必須具有在該類型上的 EXECUTE 權限。
如果未指定 type_schema_name，SQL Server Database Engine 會依照下列順序來參考 type_name：
SQL Server 系統資料類型。
目前資料庫中之目前使用者的預設結構描述。
目前資料庫中的 dbo 結構描述。
如果是編號的預存程序，資料類型不能是 xml 或 CLR 使用者定義型別。
VARYING
指定當做輸出參數支援的結果集。這個參數由預存程序動態建構，可能會有不同的內容。只適用於 cursor 參數。default
這是參數的預設值。如果定義了 default 值，不必指定該參數的值就可以執行程序。預設值必須是常數，或者，可以是 NULL。如果程序搭配 LIKE 關鍵字使用參數，則可包含下列萬用字元：% _ [] 及 [^]。[!附註]
sys.parameters.default 資料行中只會記錄 CLR 程序的預設值。如果是 Transact-SQL 程序參數，該資料行則是 NULL。
OUTPUT
指出這個參數是輸出參數。可以將這個選項的值傳回呼叫的 EXECUTE 陳述式。您可以利用 OUTPUT 參數將值傳回程序的呼叫者。除非程序是 CLR 程序，否則 text、ntext 及 image 參數不能當做 OUTPUT 參數。除非程序是一個 CLR 程序，否則，使用 OUTPUT 關鍵字的輸出參數可以是資料指標預留位置。使用者定義資料表類型無法指定為預存程序的 OUTPUT 參數。READONLY
表示無法在程序的主體內更新或修改參數。如果參數類型是使用者定義資料表類型，就必須指定 READONLY。RECOMPILE
表示 Database Engine 不快取這個程序的計畫，且程序是在執行階段編譯的。當指定 FOR REPLICATION 時，不能使用這個選項。無法為 CLR 預存程序指定 RECOMPILE。若要指示 Database Engine 捨棄預存程序內個別查詢的計畫，請使用 RECOMPILE 查詢提示。如需詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞。如果只在屬於預存程序的查詢子集中使用非典型值或暫存值，請使用 RECOMPILE 查詢提示。
ENCRYPTION
表示 SQL Server 會將 CREATE PROCEDURE 陳述式的原始文字轉換為模糊化格式。在 SQL Server 中，無法直接從任何目錄檢視中看見模糊化格式的輸出。對系統資料表或資料庫檔案沒有存取權的使用者，無法擷取模糊化的文字。不過，可以透過 DAC 通訊埠存取系統資料表，或直接存取資料庫檔案的授權使用者，則可使用該文字。另外，可將偵錯工具附加至伺服器處理序的使用者，還可以在執行階段從記憶體擷取解密程序。如需有關存取系統中繼資料的詳細資訊，請參閱＜中繼資料可見性組態＞。這個選項不適用於 CLR 預存程序。
利用這個選項建立的程序，不能發行為 SQL Server 複寫的一部分。
EXECUTE AS
指定執行預存程序時所在的安全性內容。如需詳細資訊，請參閱＜EXECUTE AS 子句 (Transact-SQL)＞。
FOR REPLICATION
指定無法針對訂閱者執行為複寫而建立的預存程序。利用 FOR REPLICATION 選項建立的預存程序，用來做為預存程序篩選，而且只有在複寫期間才會執行它。如果指定了 FOR REPLICATION，就不能宣告參數。無法為 CLR 預存程序指定 FOR REPLICATION。利用 FOR REPLICATION 建立的程序會忽略 RECOMPILE 選項。FOR REPLICATION 程序在 sys.objects 和 sys.procedures 中會有物件類型 RF。
<sql_statement>
這是包含在程序中的一個或多個 Transact-SQL 陳述式。如需有關某些適用限制的資訊，請參閱＜備註＞一節。EXTERNAL NAME assembly_name**.class_name.method_name
指定 CLR 預存程序所參考之 .NET Framework 組件的方法。class_name 必須是有效的 SQL Server 識別碼，且必須當做類別存在於組件中。如果類別包含符合命名空間的名稱 (使用句號 (.) 來分隔命名空間的各個部分)，您就必須使用方括號 ([]) 或引號 (""**) 來分隔類別名稱。指定的方法必須是類別的靜態方法。[!附註]
依預設，SQL Server 不能執行 CLR 程式碼。您可以建立、修改和卸除參考 Common Language Runtime 模組的資料庫物件；不過，在啟用 clr enabled 選項之前，您無法在 SQL Server 中執行這些參考。若要啟用這個選項，請使用 sp_configure。
備註
預存程序沒有預先定義的大小上限。
使用者定義預存程序只能在目前的資料庫中建立。暫存程序是這項規定的例外狀況，因為暫存程序一律在 tempdb 中建立。如果未指定結構描述名稱，就會使用要建立程序之使用者的預設結構描述。如需有關結構描述的詳細資訊，請參閱＜使用者結構描述分隔＞。
CREATE PROCEDURE 陳述式無法在單一批次中，與其他 Transact-SQL 陳述式結合。
依預設，參數可為 Null。如果傳遞了 NULL 參數值，且在參考的資料行不允許 NULL 值的 CREATE TABLE 或 ALTER TABLE 陳述式中使用該參數，則 Database Engine 會產生錯誤。若要防止將 NULL 傳遞至不允許 NULL 值的資料行，請將程式設計邏輯加入至程序中，或藉由 CREATE TABLE 或 ALTER TABLE 的關鍵字 DEFAULT 來使用資料行的預設值。
建議您為暫存資料表中的每個資料行明確指定 NULL 或 NOT NULL。當 CREATE TABLE 或 ALTER TABLE 陳述式中沒有指定 NULL 或 NOT NULL 屬性時，ANSI_DFLT_ON 和 ANSI_DFLT_OFF 選項可控制 Database Engine 將這些屬性指派給資料行的方式。如果某連接執行預存程序時所用的選項設定，不同於建立程序的連接所用設定，針對第二個連接建立的資料表資料行，可以有不同的 Null 屬性，且可以表現不同的行為。如果針對每個資料行明確陳述 NULL 或 NOT NULL，就會利用執行預存程序之所有連接的相同 Null 屬性來建立暫存資料表。
使用 SET 選項
當建立或修改 Transact-SQL 預存程序時，Database Engine 會將 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)，然後又設為超過 3 個字元的值，就會將資料截斷成定義的大小，而 INSERT 或 UPDATE 陳述式會繼續運作。
搭配 CLR 預存程序使用參數
CLR 預存程序的參數可以是任何一種純量 SQL Server 系統資料類型。
在 .NET Framework 中多載正確的方法時，若要讓 Database Engine 參考正確的方法，<method_specifier> 中所指出的方法必須具有下列特性：
宣告為靜態方法。
接收與程序的參數數目相同的參數數目。
不是其類別的建構函式或解構函式。
使用與 SQL Server 程序之相對應參數的資料類型相容的參數類型。如需有關使 SQL Server 資料類型符合 .NET Framework 資料類型的詳細資訊，請參閱＜對應 CLR 參數資料＞。
傳回空值，或傳回 SQLInt32、SQLInt16、System.Int32 或 System.Int16 類型的值。
如果針對任何特定參數宣告指定 OUTPUT，就會依參考 (而不是依值) 傳回它的參數。
取得預存程序的相關資訊
若要顯示 Transact-SQL 預存程序的定義，請使用程序所在資料庫中的 sys.sql_modules 目錄檢視。
例如：
USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';
[!附註]
利用 ENCRYPTION 選項建立的預存程序文字無法利用 sys.sql_modules 目錄檢視來檢視。
如需程序參考之物件的相關報告，請查詢 sys.sql_expression_dependencies 目錄檢視，或使用 sys.dm_sql_referenced_entities 和 sys.dm_sql_referencing_entities。
若要顯示 CLR 預存程序的相關資訊，請使用程序所在資料庫中的 sys.assembly_modules 目錄檢視。
若要顯示預存程序中定義之參數的相關資訊，請使用程序所在資料庫中的 sys.parameters 目錄檢視。
延遲名稱解析
您可以建立參考尚不存在之資料表的預存程序。在建立時，只會執行語法檢查。直到第一次執行預存程序時，才會編譯預存程序。只有在編譯期間才會解析預存程序中參考的所有物件。因此，仍可順利建立參考不存在之資料表中語意正確的預存程序；不過，如果參考的資料表不存在，預存程序在執行階段將會失敗。如需詳細資訊，請參閱＜延遲名稱解析及編譯＞。
執行預存程序
當您執行使用者定義預存程序時，在批次中或在使用者定義預存程序或函數之類的模組內，強烈建議您利用結構描述名稱來限定預存程序名稱。
若要寫入預存程序來接受參數值，則可以提供參數值。所提供的值必須是常數或變數。您不能指定函數名稱做為參數值。變數可以是使用者自訂變數或系統變數，如 @@SPID。
如需詳細資訊，請參閱＜執行預存程序 (Database Engine)＞。
第一次執行程序時，會編譯它來決定擷取資料的最佳存取計畫。如果已經產生的計畫仍保留在 Database Engine 的計畫快取中，則預存程序的後續執行作業可以重複使用該計畫。如需詳細資訊，請參閱＜執行計畫快取與重複使用＞。
使用 cursor 資料類型的參數
Transact-SQL 預存程序只能針對 OUTPUT 參數使用 cursor 資料類型。如果為參數指定 cursor 資料類型，則同時需要 VARYING 和 OUTPUT 參數。如果為參數指定 VARYING 關鍵字，資料類型必須是 cursor，且必須指定 OUTPUT 關鍵字。如需詳細資訊，請參閱＜在 OUTPUT 參數中使用 cursor 資料類型＞。
暫存預存程序
Database Engine 支援兩種暫存程序類型：本機和全域。只有建立本機暫存程序的連接可以看到該程序。全域暫存程序可供所有連接使用。當目前的工作階段結束時，會自動卸除本機暫存程序。當最後一個使用全域暫存程序的工作階段結束時，會卸除該程序。如需詳細資訊，請參閱＜建立預存程序 (Database Engine)＞。
自動執行預存程序
當 SQL Server 啟動時，會自動執行一個或多個預存程序。預存程序必須由 master 資料庫中的系統管理員建立，且必須在系統管理員 (sysadmin) 固定伺服器角色下執行為背景處理序。這些程序不得有任何輸入或輸出參數。如需詳細資訊，請參閱＜自動執行預存程序＞。
建立預存程序的巢狀結構
預存程序可以有巢狀結構。這表示預存程序可以呼叫另一個預存程序。被呼叫的程序開始執行時，巢狀層級會遞增；被呼叫的程序完成執行時，巢狀層級會遞減。預存程序所建立的巢狀結構最多可以有 32 個層級。如需詳細資訊，請參閱＜巢狀預存程序＞。
若要預估已編譯的預存程序大小，請使用下列效能監視器計數器。
效能監視器物件名稱 |
效能監視器計數器名稱 |
|---|---|
SQLServer：計畫快取物件 |
快取命中率 |
|
快取頁面 |
|
快取物件計數* |
* 這些計數器可供各種類別目錄的快取物件使用，包括特定 sql、已備妥的 sql、程序、觸發程序等等。
如需詳細資訊，請參閱＜SQL Server 的 Plan Cache 物件＞。
<sql_statement> 限制
除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 以外，任何 SET 陳述式都可以在預存程序中指定。這些陳述式必須是批次中唯一的陳述式。所選 SET 選項在預存程序執行期間仍然有效，然後會還原為先前的設定。
如果是預存程序擁有者以外的使用者要使用預存程序，則必須在預存程序內，利用物件結構描述的名稱來指定所有資料定義語言 (DDL) 陳述式 (如 CREATE、ALTER 或 DROP 陳述式、DBCC 陳述式、EXECUTE 和動態 SQL 陳述式) 中使用的物件名稱。如需詳細資訊，請參閱＜設計預存程序 (Database Engine)＞。
權限
需要資料庫中的 CREATE PROCEDURE 權限，以及建立程序時所在結構描述上的 ALTER 權限。
如果是 CLR 預存程序，需要 <method_specifier> 中參考之組件的擁有權，或在該組件上的 REFERENCES 權限。
範例
A. 使用簡單的程序
下列預存程序從檢視表傳回所有員工 (所提供的姓氏和名字)、工作職稱及部門名稱。這個預存程序沒有使用任何參數。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
SET NOCOUNT ON;
SELECT LastName, FirstName, Department
FROM HumanResources.vEmployeeDepartmentHistory;
GO
您可以利用下列方式執行 uspGetEmployees 預存程序：
EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;
B. 搭配參數使用簡單的程序
下列預存程序只從檢視表傳回指定的員工 (所提供的姓氏和名字)、職稱及部門名稱。這個預存程序接受與傳遞的參數完全相符的項目。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName = @FirstName AND LastName = @LastName;
GO
您可以利用下列方式執行 uspGetEmployees 預存程序：
EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
C. 搭配萬用字元參數使用簡單的程序
下列預存程序只從檢視表傳回指定的員工 (所提供的姓氏和名字)、職稱及部門。這個預存程序模式符合傳遞的參數，或者，如果沒有提供，該模式會使用預設值 (以字母 D 為開頭的姓氏)。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2
@LastName nvarchar(50) = N'D%',
@FirstName nvarchar(50) = N'%'
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO
您可以利用多種組合執行 uspGetEmployees2 預存程序。以下只顯示數種組合：
EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';
D. 傳回一個以上的結果集。
下列預存程序會傳回兩個結果集。
USE AdventureWorks;
GO
CREATE PROCEDURE uspNResults
AS
SELECT COUNT(ContactID) FROM Person.Contact
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO
E. 使用 OUTPUT 參數
下列範例會建立 uspGetList 預存程序。這個程序會傳回一份產品清單，其中產品的價格都沒有超過指定的金額。這個範例顯示多重 SELECT 陳述式和多重 OUTPUT 參數的用法。OUTPUT 參數可讓外部程序、批次，或多個 Transact-SQL 陳述式在程序執行期間存取值集。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40)
, @MaxPrice money
, @ComparePrice money OUTPUT
, @ListPrice money OUT
AS
SET NOCOUNT ON;
SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO
執行 uspGetList 以傳回成本低於 $700 的 Adventure Works 產品 (自行車) 清單。搭配流程控制語言使用 OUTPUT 參數 @Cost 和 @ComparePrices，即可在 [訊息] 視窗中傳回訊息。
[!附註]
建立程序以及使用變數時，都必須定義 OUTPUT 變數。參數名稱與變數名稱不一定要相符；不過，除非使用 @ListPrice= variable，否則，資料類型與參數定位必須相符。
DECLARE @ComparePrice money, @Cost money
EXECUTE Production.uspGetList '%Bikes%', 700,
@ComparePrice OUT,
@Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
部分結果集如下：
Product List Price
-------------------------------------------------- ------------------
Road-750 Black, 58 539.99
Mountain-500 Silver, 40 564.99
Mountain-500 Silver, 42 564.99
...
Road-750 Black, 48 539.99
Road-750 Black, 52 539.99
(14 row(s) affected)
These items can be purchased for less than $700.00.
F. 使用 WITH RECOMPILE 選項
如果提供給程序的參數不是典型的參數，且不應該在記憶體中快取或儲存新的執行計畫，WITH RECOMPILE 子句十分好用。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
SET NOCOUNT ON;
SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
FROM Purchasing.Vendor AS v
JOIN Purchasing.ProductVendor AS pv
ON v.VendorID = pv.VendorID
JOIN Production.Product AS p
ON pv.ProductID = p.ProductID
WHERE v.Name LIKE @Name;
GO
G. 使用 WITH ENCRYPTION 選項
下列範例會建立 HumanResources.uspEncryptThis 預存程序。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
SET NOCOUNT ON;
SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours
FROM HumanResources.Employee;
GO
WITH ENCRYPTION 選項會防止傳回預存程序的定義，如下列範例所示。
執行 sp_helptext：
EXEC sp_helptext 'HumanResources.uspEncryptThis';
以下為結果集：
The text for object 'HumanResources.uspEncryptThis' is encrypted.
直接查詢 sys.sql_modules 目錄檢視：
USE AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');
以下為結果集：
definition
----------------------
NULL
(1 row(s) affected)
H. 使用延遲名稱解析
下列範例會建立 uspProc1 程序。它會使用延遲名稱解析。雖然在編譯時參考的資料表不存在，仍會建立預存程序。不過，在執行程序時，資料表必須存在。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
SET NOCOUNT ON;
SELECT column1, column2 FROM table_does_not_exist
GO
若要確認已建立預存程序，請執行下列查詢：
USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');
以下為結果集：
definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
SELECT column1, column2 FROM table_does_not_exist
(1 row(s) affected)
I. 使用 EXECUTE AS 子句
下列範例顯示如何利用 EXECUTE AS 子句來指定可執行預存程序的安全性內容。在這個範例中，選項 CALLER 指定可以在呼叫程序的使用者之內容中執行程序。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL
DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON;
SELECT v.Name AS Vendor, p.Name AS 'Product name',
v.CreditRating AS 'Credit Rating',
v.ActiveFlag AS Availability
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor pv
ON v.VendorID = pv.VendorID
INNER JOIN Production.Product p
ON pv.ProductID = p.ProductID
ORDER BY v.Name ASC;
GO
J. 建立 CLR 預存程序
下列範例會建立 GetPhotoFromDB 預存程序，這個預存程序參考 HandlingLOBUsingCLR 組件之 LargeObjectBinary 類別的 GetPhotoFromDB 方法。建立預存程序之前，會先在本機資料庫中註冊 HandlingLOBUsingCLR 組件。
CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
@ProductPhotoID int,
@CurrentDirectory nvarchar(1024),
@FileName nvarchar(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO
K. 使用 OUTPUT 資料指標參數
OUTPUT 資料指標參數用來將一個特定資料指標 (對預存程序而言，它是本機資料指標) 往回傳遞至呼叫批次、預存程序或觸發程序。
首先，建立宣告的程序，然後在 Currency 資料表上開啟資料指標：
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor
@CurrencyCursor CURSOR VARYING OUTPUT
AS
SET NOCOUNT ON;
SET @CurrencyCursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT CurrencyCode, Name
FROM Sales.Currency;
OPEN @CurrencyCursor;
GO
接著，執行宣告本機資料指標變數的批次、執行程序將資料指標指派給本機變數，然後從資料指標提取資料列。
USE AdventureWorks;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO
請參閱