創(chuàng)建存儲過程。存儲過程是已保存的 Transact-SQL 語句集合，或?qū)?Microsoft .NET Framework 公共語言運行時 (CLR) 方法的引用，可接收并返回用戶提供的參數(shù)?？梢詣?chuàng)建過程供永久使用，或在一個會話（局部臨時過程）中臨時使用，或在所有會話（全局臨時過程）中臨時使用。

啟動 SQL Server 的一個實例時，也可以創(chuàng)建并自動運行存儲過程。

Transact-SQL 語法約定

語法

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ [ OUT [ PUT ]
    ] [ ,...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

備注
Transact-SQL 存儲過程的最大大小為 128 MB。

只能在當前數(shù)據(jù)庫中創(chuàng)建用戶定義存儲過程。臨時過程對此是個例外，因為它們總是在 tempdb 中創(chuàng)建。如果未指定架構名稱，則使用創(chuàng)建過程的用戶的默認架構。有關架構的詳細信息，請參閱用戶架構分離。

在單個批處理中，CREATE PROCEDURE 語句不能與其他 Transact-SQL 語句組合使用。

默認情況下，參數(shù)可為空值。如果傳遞 NULL 參數(shù)值并且在 CREATE 或 ALTER TABLE 語句中使用該參數(shù)，而該語句中被引用列又不允許使用空值，則數(shù)據(jù)庫引擎 會產(chǎn)生一個錯誤。若要阻止向不允許使用空值的列傳遞 NULL，請為過程添加編程邏輯，或使用 CREATE TABLE 或 ALTER TABLE 的 DEFAULT 關鍵字，以便對該列使用默認值。

存儲過程中的任何 CREATE TABLE 或 ALTER TABLE 語句都將自動創(chuàng)建臨時表。建議對于臨時表中的每列，顯式指定 NULL 或 NOT NULL。如果在 CREATE TABLE 或 ALTER TABLE 語句中未進行指定，則 ANSI_DFLT_ON 和 ANSI_DFLT_OFF 選項將控制數(shù)據(jù)庫引擎 為列指派 NULL 或 NOT NULL 屬性的方式。如果某個連接執(zhí)行的存儲過程對這些選項的設置與創(chuàng)建該過程的連接的設置不同，則為第二個連接創(chuàng)建的表列可能會有不同的為空性，并且顯示出不同的行為。如果為每個列顯式聲明了 NULL 或 NOT NULL，那么將對所有執(zhí)行該存儲過程的連接使用相同的為空性創(chuàng)建臨時表。

使用 SET 選項
在創(chuàng)建或修改 Transact-SQL 存儲過程時，數(shù)據(jù)庫引擎 將保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的設置。執(zhí)行存儲過程時，將使用這些原始設置。因此，所有客戶端會話的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 設置在執(zhí)行存儲過程時都將被忽略。在創(chuàng)建或更改存儲過程時不保存其他 SET 選項（例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS）。如果存儲過程的邏輯取決于特定的設置，則應在過程開頭添加一條 SET 語句，以確保設置正確。從存儲過程中執(zhí)行 SET 語句時，該設置只在存儲過程完成之前有效。之后，設置將還原為調(diào)用存儲過程時的值。這樣一來，單個客戶端就可以設置所需的選項，而不會影響存儲過程的邏輯。
注意：
在傳遞存儲過程或用戶定義函數(shù)中的參數(shù)時，或在聲明和設置批語句中的變量時，ANSI_WARNINGS 的優(yōu)先級較低。例如，如果一個變量被定義為 char(3)，但后來將該參數(shù)設置為一個大于三個字符的值，則數(shù)據(jù)將被截斷為定義的大小，并且 INSERT 或 UPDATE 語句將執(zhí)行成功。

使用 CLR 存儲過程的參數(shù)
CLR 存儲過程的參數(shù)可以是標量 SQL Server 系統(tǒng)數(shù)據(jù)類型的任何一種。

為了使數(shù)據(jù)庫引擎 在 .NET Framework 中被重載時引用正確的方法，<method_specifier> 中指示的方法必須具有下列特征：

聲明為靜態(tài)方法。

接收的參數(shù)個數(shù)與過程的參數(shù)個數(shù)相同。

不能是類的構造函數(shù)或析構函數(shù)。

使用的參數(shù)類型與 SQL Server 過程的相應參數(shù)的數(shù)據(jù)類型兼容。有關將 SQL Server 數(shù)據(jù)類型與 .NET Framework 數(shù)據(jù)類型匹配的信息，請參閱 SQL Server Data Types and Their .NET Framework Equivalents。

返回 void，或者返回類型為 SQLInt32、SQLInt16、System.Int32 或 System.Int16 的值。

如果對于任何特定的參數(shù)聲明都指定了 OUTPUT，則按照引用返回它的參數(shù)，而不是按照值返回。

獲得有關存儲過程的信息
若要顯示 Transact-SQL 存儲過程的定義，請使用該過程所在的數(shù)據(jù)庫中的 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';

注意：
不能使用 sys.sql_modules 目錄視圖查看使用 ENCRYPTION 選項創(chuàng)建的存儲過程的文本。

若要獲取有關某過程引用的對象的報表，請查詢 sys.sql_dependencies 目錄視圖或使用 sp_depends。sp_depends 不返回有關 CLR 存儲過程引用的對象的信息。若要顯示有關 CLR 存儲過程的信息，請使用該過程所在的數(shù)據(jù)庫中的 sys.assembly_modules 目錄視圖。

若要顯示有關存儲過程中定義的參數(shù)的信息，請使用該過程所在的數(shù)據(jù)庫中的 sys.parameters 目錄視圖。

延遲名稱解析
可以創(chuàng)建引用尚不存在的表的存儲過程。在創(chuàng)建時，只進行語法檢查。直到第一次執(zhí)行該存儲過程時才對其進行編譯。只有在編譯過程中才解析存儲過程中引用的所有對象。因此，如果語法正確的存儲過程引用了不存在的表，則仍可以成功創(chuàng)建；但如果引用的表不存在，則存儲過程將在運行時失敗。有關詳細信息，請參閱延遲名稱解析和編譯。

執(zhí)行存儲過程
當執(zhí)行用戶定義的存儲過程時，無論是在批中還是在模塊（例如用戶定義的存儲過程或函數(shù)）內(nèi)，極力建議使用架構名稱來限定存儲過程名。

如果存儲過程編寫為可以接受參數(shù)值，則可以提供參數(shù)值。該值必須是常量或變量。不能指定函數(shù)名作為參數(shù)值。變量可以是用戶定義變量或系統(tǒng)變量，例如 @@SPID。

有關詳細信息，請參閱執(zhí)行存儲過程（數(shù)據(jù)庫引擎）。

第一次執(zhí)行某個過程時，將編譯該過程以確定檢索數(shù)據(jù)的最優(yōu)訪問計劃。如果已經(jīng)生成的計劃仍保留在數(shù)據(jù)庫引擎 計劃緩存中，則存儲過程隨后執(zhí)行的操作可能重新使用該計劃。有關詳細信息，請參閱執(zhí)行計劃的緩存和重新使用。

使用 cursor 數(shù)據(jù)類型的參數(shù)
Transact-SQL 存儲過程只能將 cursor 數(shù)據(jù)類型用于 OUTPUT 參數(shù)。如果為某個參數(shù)指定了 cursor 數(shù)據(jù)類型，則還需要 VARYING 和 OUTPUT 參數(shù)。如果為某個參數(shù)指定了 VARYING 關鍵字，則數(shù)據(jù)類型必須是 cursor，并且必須指定 OUTPUT 關鍵字。有關詳細信息，請參閱在 OUTPUT 參數(shù)中使用 cursor 數(shù)據(jù)類型。

臨時存儲過程
數(shù)據(jù)庫引擎 支持兩種臨時過程：局部臨時過程和全局臨時過程。局部臨時過程只對創(chuàng)建該過程的連接可見。全局臨時過程則可由所有連接使用。局部臨時過程在當前會話結束時將被自動刪除。全局臨時過程在使用該過程的最后一個會話結束時被刪除。有關詳細信息，請參閱創(chuàng)建存儲過程（數(shù)據(jù)庫引擎）。

自動執(zhí)行存儲過程
SQL Server 啟動時可以自動執(zhí)行一個或多個存儲過程。這些存儲過程必須由系統(tǒng)管理員在 master 數(shù)據(jù)庫中創(chuàng)建，并以 sysadmin 固定服務器角色作為后臺進程執(zhí)行。這些過程不能有任何輸入或輸出參數(shù)。有關詳細信息，請參閱自動執(zhí)行存儲過程。

存儲過程嵌套
存儲過程可以被嵌套。這表示一個存儲過程可以調(diào)用另一個存儲過程。在被調(diào)用過程開始運行時，嵌套級將增加，在被調(diào)用過程運行結束后，嵌套級將減少。存儲過程最多可以嵌套 32 級。有關詳細信息，請參閱嵌套存儲過程。

若要估計編譯后的存儲過程大小，請使用下列性能監(jiān)視器計數(shù)器。

性能監(jiān)視器對象名 性能監(jiān)視器計數(shù)器名稱
SQLServer: Plan Cache Object
Cache Hit Ratio
Cache Pages
Cache Object Counts*

* 各種類別的緩存對象均可以使用這些計數(shù)器，包括即席 sql、準備好的 sql、過程、觸發(fā)器等。

有關詳細信息，請參閱 SQL Server Plan Cache 對象。

<sql_statement> 限制
可以在存儲過程中指定除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 以外的任何 SET 語句。這些語句在批處理中必須唯一。選擇的 SET 選項在存儲過程執(zhí)行過程中有效，之后恢復為原來的設置。

如果用戶不是存儲過程所有者，則在使用存儲過程時，必須使用對象架構名稱對存儲過程內(nèi)所有數(shù)據(jù)定義語言 (DDL) 語句（例如 CREATE、ALTER 或 DROP 語句、DBCC 語句、EXECUTE 和動態(tài) SQL 語句）中使用的對象名稱進行限定。有關詳細信息，請參閱設計存儲過程（數(shù)據(jù)庫引擎）。

參數(shù)
schema_name
過程所屬架構的名稱。

procedure_name
新存儲過程的名稱。過程名稱必須遵循有關標識符的規(guī)則，并且在架構中必須唯一。

極力建議不在過程名稱中使用前綴 sp_。此前綴由 SQL Server 使用，以指定系統(tǒng)存儲過程。有關詳細信息，請參閱創(chuàng)建存儲過程（數(shù)據(jù)庫引擎）。

可在 procedure_name 前面使用一個數(shù)字符號 (#) (#procedure_name) 來創(chuàng)建局部臨時過程，使用兩個數(shù)字符號 (##procedure_name) 來創(chuàng)建全局臨時過程。對于 CLR 存儲過程，不能指定臨時名稱。

存儲過程或全局臨時存儲過程的完整名稱（包括 ##）不能超過 128 個字符。局部臨時存儲過程的完整名稱（包括 #）不能超過 116 個字符。
; number
用于對同名過程進行分組的可選整數(shù)。使用一個 DROP PROCEDURE 語句可將這些分組過程一起刪除。例如，稱為 orders 的應用程序可能使用名為 orderproc;1、orderproc;2 等的過程。DROP PROCEDURE orderproc 語句將刪除整個組。如果名稱中包含分隔標識符，則數(shù)字不應包含在標識符中；只應在 procedure_name 前后使用適當?shù)姆指舴?/p>

帶編號的存儲過程有以下限制：
不能使用 xml 或 CLR 用戶定義類型作為數(shù)據(jù)類型。

不能對帶編號的存儲過程創(chuàng)建計劃指南。

注意：
后續(xù)版本的 Microsoft SQL Server 將刪除該功能。請避免在新的開發(fā)工作中使用該功能，并應著手修改當前還在使用該功能的應用程序。

@ parameter
過程中的參數(shù)。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數(shù)。除非定義了參數(shù)的默認值或者將參數(shù)設置為等于另一個參數(shù)，否則用戶必須在調(diào)用過程時為每個聲明的參數(shù)提供值。存儲過程最多可以有 2,100 個參數(shù)。

通過使用 at 符號 (@) 作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合有關標識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；其他過程中可以使用相同的參數(shù)名稱。默認情況下，參數(shù)只能代替常量表達式，而不能用于代替表名、列名或其他數(shù)據(jù)庫對象的名稱。有關詳細信息，請參閱 EXECUTE (Transact-SQL)。

如果指定了 FOR REPLICATION，則無法聲明參數(shù)。

[ type_schema_name. ] data_type
參數(shù)以及所屬架構的數(shù)據(jù)類型。除 table 之外的其他所有數(shù)據(jù)類型均可以用作 Transact-SQL 存儲過程的參數(shù)。但是，cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定了 cursor 數(shù)據(jù)類型，則還必須指定 VARYING 和 OUTPUT 關鍵字?？梢詾?cursor 數(shù)據(jù)類型指定多個輸出參數(shù)。

對于 CLR 存儲過程，不能指定 char、varchar、text、ntext、image、cursor 和 table 作為參數(shù)。有關 CLR 類型與 SQL Server 系統(tǒng)數(shù)據(jù)類型之間關系的詳細信息，請參閱 SQL Server Data Types and Their .NET Framework Equivalents。有關 SQL Server 系統(tǒng)數(shù)據(jù)類型及其語法的詳細信息，請參閱數(shù)據(jù)類型 (Transact-SQL)。

如果參數(shù)的數(shù)據(jù)類型為 CLR 用戶定義類型，則必須對此類型有 EXECUTE 權限。

如果未指定 type_schema_name，則 SQL Server 2005 Database Engine 將按以下順序引用 type_name：
SQL Server 系統(tǒng)數(shù)據(jù)類型。

當前數(shù)據(jù)庫中當前用戶的默認架構。

當前數(shù)據(jù)庫中的 dbo 架構。

對于帶編號的存儲過程，數(shù)據(jù)類型不能為 xml 或 CLR 用戶定義類型。

VARYING
指定作為輸出參數(shù)支持的結果集。該參數(shù)由存儲過程動態(tài)構造，其內(nèi)容可能發(fā)生改變。僅適用于 cursor 參數(shù)。

default
參數(shù)的默認值。如果定義了 default 值，則無需指定此參數(shù)的值即可執(zhí)行過程。默認值必須是常量或 NULL。如果過程使用帶 LIKE 關鍵字的參數(shù)，則可包含下列通配符：%、_、[] 和 [^]。

注意：
只有 CLR 過程的默認值記錄在 sys.parameters.default 列中。對于 Transact-SQL 過程參數(shù)，該列將為 NULL。

OUTPUT
指示參數(shù)是輸出參數(shù)。此選項的值可以返回給調(diào)用 EXECUTE 的語句。使用 OUTPUT 參數(shù)將值返回給過程的調(diào)用方。除非是 CLR 過程，否則 text、ntext 和 image 參數(shù)不能用作 OUTPUT 參數(shù)。使用 OUTPUT 關鍵字的輸出參數(shù)可以為游標占位符，CLR 過程除外。

RECOMPILE
指示數(shù)據(jù)庫引擎 不緩存該過程的計劃，該過程在運行時編譯。如果指定了 FOR REPLICATION，則不能使用此選項。對于 CLR 存儲過程，不能指定 RECOMPILE。

若要指示數(shù)據(jù)庫引擎 放棄存儲過程內(nèi)單個查詢的計劃，請使用 RECOMPILE 查詢提示。有關詳細信息，請參閱查詢提示 (Transact-SQL)。如果非典型值或臨時值僅用于屬于存儲過程的查詢子集，則使用 RECOMPILE 查詢提示。

ENCRYPTION
指示 SQL Server 將 CREATE PROCEDURE 語句的原始文本轉(zhuǎn)換為模糊格式。模糊代碼的輸出在 SQL Server 2005 的任何目錄視圖中都不能直接顯示。對系統(tǒng)表或數(shù)據(jù)庫文件沒有訪問權限的用戶不能檢索模糊文本。但是，可通過 DAC 端口訪問系統(tǒng)表的特權用戶或直接訪問數(shù)據(jù)庫文件的特權用戶可使用此文本。此外，能夠向服務器進程附加調(diào)試器的用戶可在運行時從內(nèi)存中檢索已解密的過程。

該選項對于 CLR 存儲過程無效。

注意：
使用此選項創(chuàng)建的過程不能在 SQL Server 復制過程中發(fā)布。

EXECUTE AS
指定在其中執(zhí)行存儲過程的安全上下文。

有關詳細信息，請參閱 EXECUTE AS 子句 (Transact-SQL)。

FOR REPLICATION
指定不能在訂閱服務器上執(zhí)行為復制創(chuàng)建的存儲過程。使用 FOR REPLICATION 選項創(chuàng)建的存儲過程可用作存儲過程篩選器，且只能在復制過程中執(zhí)行。如果指定了 FOR REPLICATION，則無法聲明參數(shù)。對于 CLR 存儲過程，不能指定 FOR REPLICATION。對于使用 FOR REPLICATION 創(chuàng)建的過程，忽略 RECOMPILE 選項。

FOR REPLICATION 過程將在 sys.objects 和 sys.procedures 中包含 RF 對象類型。

<sql_statement>
要包含在過程中的一個或多個 Transact-SQL 語句。有關某些適用的限制的信息，請參閱“備注”部分。

EXTERNAL NAME , assembly_name.class_name.method_name
指定 .NET Framework 程序集的方法，以便 CLR 存儲過程引用。class_name 必須為有效的 SQL Server 標識符，并且該類必須存在于程序集中。如果類包含一個使用句點 (.) 分隔命名空間各部分的限定命名空間的名稱，則必須使用方括號 ([ ]) 或引號 (" ") 將類名稱分隔開。指定的方法必須為該類的靜態(tài)方法。

注意：
默認情況下，SQL Server 不能執(zhí)行 CLR 代碼?？梢詣?chuàng)建、修改和刪除引用公共語言運行時模塊的數(shù)據(jù)庫對象；不過，只有在啟用了 clr enabled 選項之后，才能在 SQL Server 中執(zhí)行這些引用。若要啟用該選項，請使用 sp_configure。

權限
需要在數(shù)據(jù)庫中有 CREATE PROCEDURE 權限，對在其中創(chuàng)建過程的架構有 ALTER 權限。

對于 CLR 存儲過程，需要對 <method_specifier> 中引用的程序集的所有權，或擁有對該程序集的 REFERENCES 權限。

示例
A. 使用簡單過程
以下存儲過程將從視圖中返回所有雇員（提供姓和名）、職務以及部門名稱。此存儲過程不使用任何參數(shù)。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.usp_GetAllEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.usp_GetAllEmployees;
GO
CREATE PROCEDURE HumanResources.usp_GetAllEmployees
AS
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

usp_GetEmployees 存儲過程可通過以下方式執(zhí)行：

EXECUTE HumanResources.usp_GetAllEmployees;
GO
-- Or
EXEC HumanResources.usp_GetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.usp_GetAllEmployees;

B. 使用帶有參數(shù)的簡單過程
下面的存儲過程只從視圖中返回指定的雇員（提供名和姓）及其職務和部門名稱。此存儲過程接受與傳遞的參數(shù)精確匹配的值

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.usp_GetEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.usp_GetEmployees;
GO
CREATE PROCEDURE HumanResources.usp_GetEmployees
    @lastname varchar(40),
    @firstname varchar(20)
AS
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @firstname AND LastName = @lastname;
GO

usp_GetEmployees 存儲過程可通過以下方式執(zhí)行：

復制代碼
EXECUTE HumanResources.usp_GetEmployees 'Ackerman', 'Pilar';
-- Or
EXEC HumanResources.usp_GetEmployees @lastname = 'Ackerman', @firstname = 'Pilar';
GO
-- Or
EXECUTE HumanResources.usp_GetEmployees @firstname = 'Pilar', @lastname = 'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.usp_GetEmployees 'Ackerman', 'Pilar';

C. 使用帶有通配符參數(shù)的簡單過程
以下存儲過程只從視圖中返回指定的一些雇員（提供名和姓）及其職務和部門名稱。此存儲過程模式與所傳遞的參數(shù)相匹配；或者，如果未提供參數(shù)，則使用預設的默認值（以字母 D 打頭的姓）。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.usp_GetEmployees2', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.usp_GetEmployees2;
GO
CREATE PROCEDURE HumanResources.usp_GetEmployees2
    @lastname varchar(40) = 'D%',
    @firstname varchar(20) = '%'
AS
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @firstname
        AND LastName LIKE @lastname;
GO
usp_GetEmployees2 存儲過程可以按多種組合執(zhí)行。下面只顯示了幾個組合：
EXECUTE HumanResources.usp_GetEmployees2;
-- Or
EXECUTE HumanResources.usp_GetEmployees2 'Wi%';
-- Or
EXECUTE HumanResources.usp_GetEmployees2 @firstname = '%';
-- Or
EXECUTE HumanResources.usp_GetEmployees2 '[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.usp_GetEmployees2 'Hesse', 'Stefen';
-- Or
EXECUTE HumanResources.usp_GetEmployees2 'H%', 'S%';

D. 使用 OUTPUT 參數(shù)
以下示例將創(chuàng)建 usp_GetList 存儲過程。此過程將返回價格不超過指定數(shù)值的產(chǎn)品的列表。此示例顯示如何使用多個 SELECT 語句和多個 OUTPUT 參數(shù)。OUTPUT 參數(shù)允許外部過程、批處理或多條 Transact-SQL 語句在過程執(zhí)行期間訪問設置的某個值。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL
    DROP PROCEDURE Production.usp_GetList;
GO
CREATE PROCEDURE Production.usp_GetList @product varchar(40)
    , @maxprice money
    , @compareprice money OUTPUT
    , @listprice money OUT
AS
    SELECT p.name AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product p
    JOIN Production.ProductSubcategory s
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.name LIKE @product AND p.ListPrice < @maxprice;
-- Populate the output variable @listprice.
SET @listprice = (SELECT MAX(p.ListPrice)
        FROM Production.Product p
        JOIN Production.ProductSubcategory s
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.name LIKE @product AND p.ListPrice < @maxprice);
-- Populate the output variable @compareprice.
SET @compareprice = @maxprice;
GO

執(zhí)行 usp_GetList，返回價格低于 $700 的 Adventure Works 產(chǎn)品（自行車）的列表。OUTPUT 參數(shù) @cost 和 @compareprices 用于流控制語言，以便在“消息”窗口中返回消息。

注意：
OUTPUT 變量必須在創(chuàng)建過程時或使用變量時定義。參數(shù)名和變量名不一定要匹配；但是，除非使用 @listprice = variable 的形式，否則數(shù)據(jù)類型和參數(shù)位置必須匹配。

DECLARE @compareprice money, @cost money
EXECUTE Production.usp_GetList '%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.

E. 使用 WITH RECOMPILE 選項
如果為過程提供的參數(shù)不是典型的參數(shù)，并且新的執(zhí)行計劃不應被緩存或存儲在內(nèi)存中，則 WITH RECOMPILE 子句會很有用。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.usp_product_by_vendor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.usp_product_by_vendor;
GO
CREATE PROCEDURE dbo.usp_product_by_vendor @name varchar(30) = '%'
WITH RECOMPILE
AS
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor v
    JOIN Purchasing.ProductVendor pv
      ON v.VendorID = pv.VendorID
    JOIN Production.Product p
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @name;
GO

F. 使用 WITH ENCRYPTION 選項
以下示例將創(chuàng)建 HumanResources.usp_encrypt_this 存儲過程。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.usp_encrypt_this', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.usp_encrypt_this;
GO
CREATE PROCEDURE HumanResources.usp_encrypt_this
WITH ENCRYPTION
AS
    SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours
    FROM HumanResources.Employee;
GO

如以下示例所示，WITH ENCRYPTION 選項可阻止返回存儲過程的定義。

運行 sp_helptext：

EXEC sp_helptext 'HumanResources.usp_encrypt_this';

下面是結果集：

The text for object 'HumanResources.usp_encrypt_this' is encrypted.

直接查詢 sys.sql_modules 目錄視圖：

USE AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.usp_encrypt_this');

下面是結果集：

definition
----------------------
NULL

(1 row(s) affected)

G. 使用延遲名稱解析
以下示例將創(chuàng)建 usp_proc1 過程。該過程使用延遲名稱解析。盡管引用的表在編譯時不存在，但仍能創(chuàng)建存儲過程。但是，執(zhí)行過程時表必須存在。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.usp_proc1', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.usp_proc1;
GO
CREATE PROCEDURE dbo.usp_proc1
AS
    SELECT column1, column2 FROM table_does_not_exist
GO

若要驗證是否已創(chuàng)建了存儲過程，請運行以下查詢：

USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.usp_proc1');

下面是結果集：
definition
-----------------------------------------------------------------------
CREATE PROCEDURE usp_proc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)
H. 使用 EXECUTE AS 子句
以下示例顯示使用 EXECUTE AS 子句指定執(zhí)行存儲過程的安全上下文。在此示例中，選項 CALLER 指定此過程可在調(diào)用它的用戶上下文中執(zhí)行。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Purchasing.usp_vendor_info_all', 'P' ) IS NOT NULL
    DROP PROCEDURE Purchasing.usp_vendor_info_all;
GO
CREATE PROCEDURE Purchasing.usp_vendor_info_all
WITH EXECUTE AS CALLER
AS
    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

I. 創(chuàng)建 CLR 存儲過程
以下示例將創(chuàng)建 GetPhotoFromDB 存儲過程，此過程引用 HandlingLOBUsingCLR 程序集中的 LargeObjectBinary 類的 GetPhotoFromDB 方法。創(chuàng)建存儲過程前，需要在本地數(shù)據(jù)庫中注冊 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

J. 使用 OUTPUT 游標參數(shù)
OUTPUT 游標參數(shù)用來將存儲過程的局部游標傳遞回執(zhí)行調(diào)用的批處理、存儲過程或觸發(fā)器。

首先，創(chuàng)建以下過程：在 titles 表上聲明并打開一個游標：

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.currency_cursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.currency_cursor;
GO
CREATE PROCEDURE dbo.currency_cursor
    @currency_cursor CURSOR VARYING OUTPUT
AS
    SET @currency_cursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @currency_cursor;
GO

接下來，運行以下批處理：聲明一個局部游標變量，執(zhí)行上述過程以將游標賦值給局部變量，然后從該游標提取行。

USE AdventureWorks;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.currency_cursor @currency_cursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO