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