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