創(chuàng)建存儲過程。存儲過程是已保存的 Transact-SQL 語句集合，或?qū)?Microsoft .NET Framework 公共語言運(yùn)行時 (CLR) 方法的引用，可接收并返回用戶提供的參數(shù)?？梢詣?chuàng)建過程供永久使用，或在一個會話（局部臨時過程）中臨時使用，或在所有會話（全局臨時過程）中臨時使用。
啟動 SQL Server 的一個實(shí)例時，也可以創(chuàng)建并自動運(yùn)行存儲過程。
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。
只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建用戶定義存儲過程。臨時過程對此是個例外，因?yàn)樗鼈兛偸窃?tempdb 中創(chuàng)建。如果未指定架構(gòu)名稱，則使用創(chuàng)建過程的用戶的默認(rèn)架構(gòu)。有關(guān)架構(gòu)的詳細(xì)信息，請參閱用戶架構(gòu)分離。
在單個批處理中，CREATE PROCEDURE 語句不能與其他 Transact-SQL 語句組合使用。
默認(rèn)情況下，參數(shù)可為空值。如果傳遞 NULL 參數(shù)值并且在 CREATE 或 ALTER TABLE 語句中使用該參數(shù)，而該語句中被引用列又不允許使用空值，則數(shù)據(jù)庫引擎 會產(chǎn)生一個錯誤。若要阻止向不允許使用空值的列傳遞 NULL，請為過程添加編程邏輯，或使用 CREATE TABLE 或 ALTER TABLE 的 DEFAULT 關(guān)鍵字，以便對該列使用默認(rèn)值。
存儲過程中的任何 CREATE TABLE 或 ALTER TABLE 語句都將自動創(chuàng)建臨時表。建議對于臨時表中的每列，顯式指定 NULL 或 NOT NULL。如果在 CREATE TABLE 或 ALTER TABLE 語句中未進(jìn)行指定，則 ANSI_DFLT_ON 和 ANSI_DFLT_OFF 選項(xiàng)將控制數(shù)據(jù)庫引擎 為列指派 NULL 或 NOT NULL 屬性的方式。如果某個連接執(zhí)行的存儲過程對這些選項(xiàng)的設(shè)置與創(chuàng)建該過程的連接的設(shè)置不同，則為第二個連接創(chuàng)建的表列可能會有不同的為空性，并且顯示出不同的行為。如果為每個列顯式聲明了 NULL 或 NOT NULL，那么將對所有執(zhí)行該存儲過程的連接使用相同的為空性創(chuàng)建臨時表。
使用 SET 選項(xiàng)
在創(chuàng)建或修改 Transact-SQL 存儲過程時，數(shù)據(jù)庫引擎 將保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的設(shè)置。執(zhí)行存儲過程時，將使用這些原始設(shè)置。因此，所有客戶端會話的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 設(shè)置在執(zhí)行存儲過程時都將被忽略。在創(chuàng)建或更改存儲過程時不保存其他 SET 選項(xiàng)（例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS）。如果存儲過程的邏輯取決于特定的設(shè)置，則應(yīng)在過程開頭添加一條 SET 語句，以確保設(shè)置正確。從存儲過程中執(zhí)行 SET 語句時，該設(shè)置只在存儲過程完成之前有效。之后，設(shè)置將還原為調(diào)用存儲過程時的值。這樣一來，單個客戶端就可以設(shè)置所需的選項(xiàng)，而不會影響存儲過程的邏輯。
注意：
在傳遞存儲過程或用戶定義函數(shù)中的參數(shù)時，或在聲明和設(shè)置批語句中的變量時，ANSI_WARNINGS 的優(yōu)先級較低。例如，如果一個變量被定義為 char(3)，但后來將該參數(shù)設(shè)置為一個大于三個字符的值，則數(shù)據(jù)將被截斷為定義的大小，并且 INSERT 或 UPDATE 語句將執(zhí)行成功。
使用 CLR 存儲過程的參數(shù)
CLR 存儲過程的參數(shù)可以是標(biāo)量 SQL Server 系統(tǒng)數(shù)據(jù)類型的任何一種。
為了使數(shù)據(jù)庫引擎 在 .NET Framework 中被重載時引用正確的方法，<method_specifier> 中指示的方法必須具有下列特征：
聲明為靜態(tài)方法。
接收的參數(shù)個數(shù)與過程的參數(shù)個數(shù)相同。
不能是類的構(gòu)造函數(shù)或析構(gòu)函數(shù)。
使用的參數(shù)類型與 SQL Server 過程的相應(yīng)參數(shù)的數(shù)據(jù)類型兼容。有關(guān)將 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ù)，而不是按照值返回。
獲得有關(guān)存儲過程的信息
若要顯示 Transact-SQL 存儲過程的定義，請使用該過程所在的數(shù)據(jù)庫中的 sys.sql_modules 目錄視圖。
例如：
復(fù)制代碼
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 選項(xiàng)創(chuàng)建的存儲過程的文本。
若要獲取有關(guān)某過程引用的對象的報表，請查詢 sys.sql_dependencies 目錄視圖或使用 sp_depends。sp_depends 不返回有關(guān) CLR 存儲過程引用的對象的信息。若要顯示有關(guān) CLR 存儲過程的信息，請使用該過程所在的數(shù)據(jù)庫中的 sys.assembly_modules 目錄視圖。
若要顯示有關(guān)存儲過程中定義的參數(shù)的信息，請使用該過程所在的數(shù)據(jù)庫中的 sys.parameters 目錄視圖。
延遲名稱解析
可以創(chuàng)建引用尚不存在的表的存儲過程。在創(chuàng)建時，只進(jìn)行語法檢查。直到第一次執(zhí)行該存儲過程時才對其進(jìn)行編譯。只有在編譯過程中才解析存儲過程中引用的所有對象。因此，如果語法正確的存儲過程引用了不存在的表，則仍可以成功創(chuàng)建；但如果引用的表不存在，則存儲過程將在運(yùn)行時失敗。有關(guān)詳細(xì)信息，請參閱延遲名稱解析和編譯。
執(zhí)行存儲過程
當(dāng)執(zhí)行用戶定義的存儲過程時，無論是在批中還是在模塊（例如用戶定義的存儲過程或函數(shù)）內(nèi)，極力建議使用架構(gòu)名稱來限定存儲過程名。
如果存儲過程編寫為可以接受參數(shù)值，則可以提供參數(shù)值。該值必須是常量或變量。不能指定函數(shù)名作為參數(shù)值。變量可以是用戶定義變量或系統(tǒng)變量，例如 @@SPID。
有關(guān)詳細(xì)信息，請參閱執(zhí)行存儲過程（數(shù)據(jù)庫引擎）。
第一次執(zhí)行某個過程時，將編譯該過程以確定檢索數(shù)據(jù)的最優(yōu)訪問計劃。如果已經(jīng)生成的計劃仍保留在數(shù)據(jù)庫引擎 計劃緩存中，則存儲過程隨后執(zhí)行的操作可能重新使用該計劃。有關(guān)詳細(xì)信息，請參閱執(zhí)行計劃的緩存和重新使用。
使用 cursor 數(shù)據(jù)類型的參數(shù)
Transact-SQL 存儲過程只能將 cursor 數(shù)據(jù)類型用于 OUTPUT 參數(shù)。如果為某個參數(shù)指定了 cursor 數(shù)據(jù)類型，則還需要 VARYING 和 OUTPUT 參數(shù)。如果為某個參數(shù)指定了 VARYING 關(guān)鍵字，則數(shù)據(jù)類型必須是 cursor，并且必須指定 OUTPUT 關(guān)鍵字。有關(guān)詳細(xì)信息，請參閱在 OUTPUT 參數(shù)中使用 cursor 數(shù)據(jù)類型。
臨時存儲過程
數(shù)據(jù)庫引擎 支持兩種臨時過程：局部臨時過程和全局臨時過程。局部臨時過程只對創(chuàng)建該過程的連接可見。全局臨時過程則可由所有連接使用。局部臨時過程在當(dāng)前會話結(jié)束時將被自動刪除。全局臨時過程在使用該過程的最后一個會話結(jié)束時被刪除。有關(guān)詳細(xì)信息，請參閱創(chuàng)建存儲過程（數(shù)據(jù)庫引擎）。
自動執(zhí)行存儲過程
SQL Server 啟動時可以自動執(zhí)行一個或多個存儲過程。這些存儲過程必須由系統(tǒng)管理員在 master 數(shù)據(jù)庫中創(chuàng)建，并以 sysadmin 固定服務(wù)器角色作為后臺進(jìn)程執(zhí)行。這些過程不能有任何輸入或輸出參數(shù)。有關(guān)詳細(xì)信息，請參閱自動執(zhí)行存儲過程。
存儲過程嵌套
存儲過程可以被嵌套。這表示一個存儲過程可以調(diào)用另一個存儲過程。在被調(diào)用過程開始運(yùn)行時，嵌套級將增加，在被調(diào)用過程運(yùn)行結(jié)束后，嵌套級將減少。存儲過程最多可以嵌套 32 級。有關(guān)詳細(xì)信息，請參閱嵌套存儲過程。
若要估計編譯后的存儲過程大小，請使用下列性能監(jiān)視器計數(shù)器。
性能監(jiān)視器對象名 性能監(jiān)視器計數(shù)器名稱
SQLServer: Plan Cache Object
Cache Hit Ratio
Cache Pages
Cache Object Counts*
* 各種類別的緩存對象均可以使用這些計數(shù)器，包括即席 sql、準(zhǔn)備好的 sql、過程、觸發(fā)器等。
有關(guān)詳細(xì)信息，請參閱 SQL Server Plan Cache 對象。
<sql_statement> 限制
可以在存儲過程中指定除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 以外的任何 SET 語句。這些語句在批處理中必須唯一。選擇的 SET 選項(xiàng)在存儲過程執(zhí)行過程中有效，之后恢復(fù)為原來的設(shè)置。
如果用戶不是存儲過程所有者，則在使用存儲過程時，必須使用對象架構(gòu)名稱對存儲過程內(nèi)所有數(shù)據(jù)定義語言 (DDL) 語句（例如 CREATE、ALTER 或 DROP 語句、DBCC 語句、EXECUTE 和動態(tài) SQL 語句）中使用的對象名稱進(jìn)行限定。有關(guān)詳細(xì)信息，請參閱設(shè)計存儲過程（數(shù)據(jù)庫引擎）。
參數(shù)
schema_name
過程所屬架構(gòu)的名稱。
procedure_name
新存儲過程的名稱。過程名稱必須遵循有關(guān)標(biāo)識符的規(guī)則，并且在架構(gòu)中必須唯一。
極力建議不在過程名稱中使用前綴 sp_。此前綴由 SQL Server 使用，以指定系統(tǒng)存儲過程。有關(guān)詳細(xì)信息，請參閱創(chuàng)建存儲過程（數(shù)據(jù)庫引擎）。
可在 procedure_name 前面使用一個數(shù)字符號 (#) (#procedure_name) 來創(chuàng)建局部臨時過程，使用兩個數(shù)字符號 (##procedure_name) 來創(chuàng)建全局臨時過程。對于 CLR 存儲過程，不能指定臨時名稱。
存儲過程或全局臨時存儲過程的完整名稱（包括 ##）不能超過 128 個字符。局部臨時存儲過程的完整名稱（包括 #）不能超過 116 個字符。
; number
用于對同名過程進(jìn)行分組的可選整數(shù)。使用一個 DROP PROCEDURE 語句可將這些分組過程一起刪除。例如，稱為 orders 的應(yīng)用程序可能使用名為 orderproc;1、orderproc;2 等的過程。DROP PROCEDURE orderproc 語句將刪除整個組。如果名稱中包含分隔標(biāo)識符，則數(shù)字不應(yīng)包含在標(biāo)識符中；只應(yīng)在 procedure_name 前后使用適當(dāng)?shù)姆指舴?/p>
帶編號的存儲過程有以下限制：
不能使用 xml 或 CLR 用戶定義類型作為數(shù)據(jù)類型。
不能對帶編號的存儲過程創(chuàng)建計劃指南。
注意：
后續(xù)版本的 Microsoft SQL Server 將刪除該功能。請避免在新的開發(fā)工作中使用該功能，并應(yīng)著手修改當(dāng)前還在使用該功能的應(yīng)用程序。
@ parameter
過程中的參數(shù)。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數(shù)。除非定義了參數(shù)的默認(rèn)值或者將參數(shù)設(shè)置為等于另一個參數(shù)，否則用戶必須在調(diào)用過程時為每個聲明的參數(shù)提供值。存儲過程最多可以有 2,100 個參數(shù)。
通過使用 at 符號 (@) 作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合有關(guān)標(biāo)識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；其他過程中可以使用相同的參數(shù)名稱。默認(rèn)情況下，參數(shù)只能代替常量表達(dá)式，而不能用于代替表名、列名或其他數(shù)據(jù)庫對象的名稱。有關(guān)詳細(xì)信息，請參閱 EXECUTE (Transact-SQL)。
如果指定了 FOR REPLICATION，則無法聲明參數(shù)。
[ type_schema_name. ] data_type
參數(shù)以及所屬架構(gòu)的數(shù)據(jù)類型。除 table 之外的其他所有數(shù)據(jù)類型均可以用作 Transact-SQL 存儲過程的參數(shù)。但是，cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定了 cursor 數(shù)據(jù)類型，則還必須指定 VARYING 和 OUTPUT 關(guān)鍵字?？梢詾?cursor 數(shù)據(jù)類型指定多個輸出參數(shù)。
對于 CLR 存儲過程，不能指定 char、varchar、text、ntext、image、cursor 和 table 作為參數(shù)。有關(guān) CLR 類型與 SQL Server 系統(tǒng)數(shù)據(jù)類型之間關(guān)系的詳細(xì)信息，請參閱 SQL Server Data Types and Their .NET Framework Equivalents。有關(guān) SQL Server 系統(tǒng)數(shù)據(jù)類型及其語法的詳細(xì)信息，請參閱數(shù)據(jù)類型 (Transact-SQL)。
如果參數(shù)的數(shù)據(jù)類型為 CLR 用戶定義類型，則必須對此類型有 EXECUTE 權(quán)限。
如果未指定 type_schema_name，則 SQL Server 2005 Database Engine 將按以下順序引用 type_name：
SQL Server 系統(tǒng)數(shù)據(jù)類型。
當(dāng)前數(shù)據(jù)庫中當(dāng)前用戶的默認(rèn)架構(gòu)。
當(dāng)前數(shù)據(jù)庫中的 dbo 架構(gòu)。
對于帶編號的存儲過程，數(shù)據(jù)類型不能為 xml 或 CLR 用戶定義類型。
VARYING
指定作為輸出參數(shù)支持的結(jié)果集。該參數(shù)由存儲過程動態(tài)構(gòu)造，其內(nèi)容可能發(fā)生改變。僅適用于 cursor 參數(shù)。
default
參數(shù)的默認(rèn)值。如果定義了 default 值，則無需指定此參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或 NULL。如果過程使用帶 LIKE 關(guān)鍵字的參數(shù)，則可包含下列通配符：%、_、[] 和 [^]。
注意：
只有 CLR 過程的默認(rèn)值記錄在 sys.parameters.default 列中。對于 Transact-SQL 過程參數(shù)，該列將為 NULL。
OUTPUT
指示參數(shù)是輸出參數(shù)。此選項(xiàng)的值可以返回給調(diào)用 EXECUTE 的語句。使用 OUTPUT 參數(shù)將值返回給過程的調(diào)用方。除非是 CLR 過程，否則 text、ntext 和 image 參數(shù)不能用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以為游標(biāo)占位符，CLR 過程除外。
RECOMPILE
指示數(shù)據(jù)庫引擎 不緩存該過程的計劃，該過程在運(yùn)行時編譯。如果指定了 FOR REPLICATION，則不能使用此選項(xiàng)。對于 CLR 存儲過程，不能指定 RECOMPILE。
若要指示數(shù)據(jù)庫引擎 放棄存儲過程內(nèi)單個查詢的計劃，請使用 RECOMPILE 查詢提示。有關(guān)詳細(xì)信息，請參閱查詢提示 (Transact-SQL)。如果非典型值或臨時值僅用于屬于存儲過程的查詢子集，則使用 RECOMPILE 查詢提示。
ENCRYPTION
指示 SQL Server 將 CREATE PROCEDURE 語句的原始文本轉(zhuǎn)換為模糊格式。模糊代碼的輸出在 SQL Server 2005 的任何目錄視圖中都不能直接顯示。對系統(tǒng)表或數(shù)據(jù)庫文件沒有訪問權(quán)限的用戶不能檢索模糊文本。但是，可通過 DAC 端口訪問系統(tǒng)表的特權(quán)用戶或直接訪問數(shù)據(jù)庫文件的特權(quán)用戶可使用此文本。此外，能夠向服務(wù)器進(jìn)程附加調(diào)試器的用戶可在運(yùn)行時從內(nèi)存中檢索已解密的過程。
該選項(xiàng)對于 CLR 存儲過程無效。
注意：
使用此選項(xiàng)創(chuàng)建的過程不能在 SQL Server 復(fù)制過程中發(fā)布。
EXECUTE AS
指定在其中執(zhí)行存儲過程的安全上下文。
有關(guān)詳細(xì)信息，請參閱 EXECUTE AS 子句 (Transact-SQL)。
FOR REPLICATION
指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程。使用 FOR REPLICATION 選項(xiàng)創(chuàng)建的存儲過程可用作存儲過程篩選器，且只能在復(fù)制過程中執(zhí)行。如果指定了 FOR REPLICATION，則無法聲明參數(shù)。對于 CLR 存儲過程，不能指定 FOR REPLICATION。對于使用 FOR REPLICATION 創(chuàng)建的過程，忽略 RECOMPILE 選項(xiàng)。
FOR REPLICATION 過程將在 sys.objects 和 sys.procedures 中包含 RF 對象類型。
<sql_statement>
要包含在過程中的一個或多個 Transact-SQL 語句。有關(guān)某些適用的限制的信息，請參閱“備注”部分。
EXTERNAL NAME , assembly_name.class_name.method_name
指定 .NET Framework 程序集的方法，以便 CLR 存儲過程引用。class_name 必須為有效的 SQL Server 標(biāo)識符，并且該類必須存在于程序集中。如果類包含一個使用句點(diǎn) (.) 分隔命名空間各部分的限定命名空間的名稱，則必須使用方括號 ([ ]) 或引號 (" ") 將類名稱分隔開。指定的方法必須為該類的靜態(tài)方法。
注意：
默認(rèn)情況下，SQL Server 不能執(zhí)行 CLR 代碼。可以創(chuàng)建、修改和刪除引用公共語言運(yùn)行時模塊的數(shù)據(jù)庫對象；不過，只有在啟用了 clr enabled 選項(xiàng)之后，才能在 SQL Server 中執(zhí)行這些引用。若要啟用該選項(xiàng)，請使用 sp_configure。
權(quán)限
需要在數(shù)據(jù)庫中有 CREATE PROCEDURE 權(quán)限，對在其中創(chuàng)建過程的架構(gòu)有 ALTER 權(quán)限。
對于 CLR 存儲過程，需要對 <method_specifier> 中引用的程序集的所有權(quán)，或擁有對該程序集的 REFERENCES 權(quán)限。
示例
A. 使用簡單過程
以下存儲過程將從視圖中返回所有雇員（提供姓和名）、職務(wù)以及部門名稱。此存儲過程不使用任何參數(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ù)的簡單過程
下面的存儲過程只從視圖中返回指定的雇員（提供名和姓）及其職務(wù)和部門名稱。此存儲過程接受與傳遞的參數(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í)行：
復(fù)制代碼
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ù)的簡單過程
以下存儲過程只從視圖中返回指定的一些雇員（提供名和姓）及其職務(wù)和部門名稱。此存儲過程模式與所傳遞的參數(shù)相匹配；或者，如果未提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值（以字母 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í)行期間訪問設(shè)置的某個值。
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)))+'.'
下面是部分結(jié)果集：
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 選項(xiàng)
如果為過程提供的參數(shù)不是典型的參數(shù)，并且新的執(zhí)行計劃不應(yīng)被緩存或存儲在內(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 選項(xiàng)
以下示例將創(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 選項(xiàng)可阻止返回存儲過程的定義。
運(yùn)行 sp_helptext：
EXEC sp_helptext 'HumanResources.usp_encrypt_this';
下面是結(jié)果集：
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');
下面是結(jié)果集：
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
若要驗(yàn)證是否已創(chuàng)建了存儲過程，請運(yùn)行以下查詢：
USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.usp_proc1');
下面是結(jié)果集：
definition
-----------------------------------------------------------------------
CREATE PROCEDURE usp_proc1
AS
SELECT column1, column2 FROM table_does_not_exist
(1 row(s) affected)
H. 使用 EXECUTE AS 子句
以下示例顯示使用 EXECUTE AS 子句指定執(zhí)行存儲過程的安全上下文。在此示例中，選項(xiàng) 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 游標(biāo)參數(shù)
OUTPUT 游標(biāo)參數(shù)用來將存儲過程的局部游標(biāo)傳遞回執(zhí)行調(diào)用的批處理、存儲過程或觸發(fā)器。
首先，創(chuàng)建以下過程：在 titles 表上聲明并打開一個游標(biāo)：
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
接下來，運(yùn)行以下批處理：聲明一個局部游標(biāo)變量，執(zhí)行上述過程以將游標(biāo)賦值給局部變量，然后從該游標(biāo)提取行。
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
