CREATE PROCEDURE
創(chuàng)建存儲過程，存儲過程是保存起來的可以接受和返回用戶提供的參數(shù)的 Transact-SQL 語句的集合。可以創(chuàng)建一個過程供永久使用，或在一個會話中臨時使用（局部臨時過程），或在所有會話中臨時使用（全局臨時過程）。也可以創(chuàng)建在 Microsoft SQL Server啟動時自動運行的存儲過程。

語法
CREATE PROC [ EDURE ] procedure_name [ ; number ]
??? [ { @parameter data_type }
??????? [ VARYING ] [ = default ] [ OUTPUT ]
??? ] [ ,...n ]

[ WITH
??? { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

參數(shù)
procedure_name

??? 新存儲過程的名稱。過程名必須符合標(biāo)識符規(guī)則，且對于數(shù)據(jù)庫及其所有者必須唯一。有關(guān)更多信息，請參見使用標(biāo)識符。
??? 要創(chuàng)建局部臨時過程，可以在 procedure_name 前面加一個編號符 (#procedure_name)，要創(chuàng)建全局臨時過程，可以在 procedure_name 前面加兩個編號符 (##procedure_name)。完整的名稱（包括 # 或 ##）不能超過 128 個字符。指定過程所有者的名稱是可選的。

;number
??? 是可選的整數(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>

@parameter

過程中的參數(shù)。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數(shù)。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。存儲過程最多可以有 2.100 個參數(shù)。

使用 @ 符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以用在其它過程中。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。有關(guān)更多信息，請參見 EXECUTE。

data_type

參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括 text、ntext 和 image）均可以用作存儲過程的參數(shù)。不過，cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定的數(shù)據(jù)類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關(guān)鍵字。有關(guān) SQL Server 提供的數(shù)據(jù)類型及其語法的更多信息，請參見數(shù)據(jù)類型。

說明? 對于可以是 cursor 數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限制。

VARYING

指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。

default

參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或 NULL。如果過程將對該參數(shù)使用 LIKE 關(guān)鍵字，那么默認(rèn)值中可以包含通配符（%、_、[] 和 [^]）。

OUTPUT

表明參數(shù)是返回參數(shù)。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過程。Text、ntext 和 image 參數(shù)可用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符。

n

表示最多可以指定 2.100 個參數(shù)的占位符。

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE 表明 SQL Server 不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計劃時，請使用 RECOMPILE 選項。

ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復(fù)制的一部分發(fā)布。

說明? 在升級過程中，SQL Server 利用存儲在 syscomments 中的加密注釋來重新創(chuàng)建加密過程。

FOR REPLICATION

指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程。.使用 FOR REPLICATION 選項創(chuàng)建的存儲過程可用作存儲過程篩選，且只能在復(fù)制過程中執(zhí)行。本選項不能和 WITH RECOMPILE 選項一起使用。

AS

指定過程要執(zhí)行的操作。

sql_statement

過程中要包含的任意數(shù)目和類型的 Transact-SQL 語句。但有一些限制。

n

是表示此過程可以包含多條 Transact-SQL 語句的占位符。

注釋
存儲過程的最大大小為 128 MB。

用戶定義的存儲過程只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建（臨時過程除外，臨時過程總是在 tempdb 中創(chuàng)建）。在單個批處理中，CREATE PROCEDURE 語句不能與其它 Transact-SQL 語句組合使用。

默認(rèn)情況下，參數(shù)可為空。如果傳遞 NULL 參數(shù)值并且該參數(shù)在 CREATE 或 ALTER TABLE 語句中使用，而該語句中引用的列又不允許使用 NULL，則 SQL Server 會產(chǎn)生一條錯誤信息。為了防止向不允許使用 NULL 的列傳遞 NULL 參數(shù)值，應(yīng)向過程中添加編程邏輯或為該列使用默認(rèn)值（使用 CREATE 或 ALTER TABLE 的 DEFAULT 關(guān)鍵字)。

建議在存儲過程的任何 CREATE TABLE 或 ALTER TABLE 語句中都為每列顯式指定 NULL 或 NOT NULL，例如在創(chuàng)建臨時表時。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 選項控制 SQL Server 為列指派 NULL 或 NOT NULL 特性的方式（如果在 CREATE TABLE 或 ALTER TABLE 語句中沒有指定的話）。如果某個連接執(zhí)行的存儲過程對這些選項的設(shè)置與創(chuàng)建該過程的連接的設(shè)置不同，則為第二個連接創(chuàng)建的表列可能會有不同的為空性，并且表現(xiàn)出不同的行為方式。如果為每個列顯式聲明了 NULL 或 NOT NULL，那么將對所有執(zhí)行該存儲過程的連接使用相同的為空性創(chuàng)建臨時表。

在創(chuàng)建或更改存儲過程時，SQL Server 將保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的設(shè)置。執(zhí)行存儲過程時，將使用這些原始設(shè)置。因此，所有客戶端會話的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 設(shè)置在執(zhí)行存儲過程時都將被忽略。在存儲過程中出現(xiàn)的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 語句不影響存儲過程的功能。

其它 SET 選項（例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS）在創(chuàng)建或更改存儲過程時不保存。如果存儲過程的邏輯取決于特定的設(shè)置，應(yīng)在過程開頭添加一條 SET 語句，以確保設(shè)置正確。從存儲過程中執(zhí)行 SET 語句時，該設(shè)置只在存儲過程完成之前有效。之后，設(shè)置將恢復(fù)為調(diào)用存儲過程時的值。這使個別的客戶端可以設(shè)置所需的選項，而不會影響存儲過程的邏輯。

說明? SQL Server 是將空字符串解釋為單個空格還是解釋為真正的空字符串，由兼容級別設(shè)置控制。如果兼容級別小于或等于 65，SQL Server 就將空字符串解釋為單個空格。如果兼容級別等于 70，則 SQL Server 將空字符串解釋為空字符串。有關(guān)更多信息，請參見 sp_dbcmptlevel。

獲得有關(guān)存儲過程的信息
若要顯示用來創(chuàng)建過程的文本，請在過程所在的數(shù)據(jù)庫中執(zhí)行 sp_helptext，并使用過程名作為參數(shù)。

說明? 使用 ENCRYPTION 選項創(chuàng)建的存儲過程不能使用 sp_helptext 查看。

若要顯示有關(guān)過程引用的對象的報表，請使用 sp_depends。

若要為過程重命名，請使用 sp_rename。

引用對象
SQL Server 允許創(chuàng)建的存儲過程引用尚不存在的對象。在創(chuàng)建時，只進(jìn)行語法檢查。執(zhí)行時，如果高速緩存中尚無有效的計劃，則編譯存儲過程以生成執(zhí)行計劃。只有在編譯過程中才解析存儲過程中引用的所有對象。因此，如果語法正確的存儲過程引用了不存在的對象，則仍可以成功創(chuàng)建，但在運行時將失敗，因為所引用的對象不存在。有關(guān)更多信息，請參見延遲名稱解析和編譯。

延遲名稱解析和兼容級別
SQL Server 允許 Transact-SQL 存儲過程在創(chuàng)建時引用不存在的表。這種能力稱為延遲名稱解析。不過，如果 Transact-SQL 存儲過程引用了該存儲過程中定義的表，而兼容級別設(shè)置（通過執(zhí)行 sp_dbcmptlevel 來設(shè)置）為 65，則在創(chuàng)建時會發(fā)出警告信息。而如果在運行時所引用的表不存在，將返回錯誤信息。有關(guān)更多信息，請參見 sp_dbcmptlevel 和延遲名稱解析和編譯。

執(zhí)行存儲過程
成功執(zhí)行 CREATE PROCEDURE 語句后，過程名稱將存儲在 sysobjects 系統(tǒng)表中，而 CREATE PROCEDURE 語句的文本將存儲在 syscomments 中。第一次執(zhí)行時，將編譯該過程以確定檢索數(shù)據(jù)的最佳訪問計劃。

使用 cursor 數(shù)據(jù)類型的參數(shù)
存儲過程只能將 cursor 數(shù)據(jù)類型用于 OUTPUT 參數(shù)。如果為某個參數(shù)指定了 cursor 數(shù)據(jù)類型，也必須指定 VARYING 和 OUTPUT 參數(shù)。如果為某個參數(shù)指定了 VARYING 關(guān)鍵字，則數(shù)據(jù)類型必須是 cursor，并且必須指定 OUTPUT 關(guān)鍵字。

說明? cursor 數(shù)據(jù)類型不能通過數(shù)據(jù)庫 API（例如 OLE DB、ODBC、ADO 和 DB-Library）綁定到應(yīng)用程序變量上。因為必須先綁定 OUTPUT 參數(shù)，應(yīng)用程序才可以執(zhí)行存儲過程，所以帶有 cursor OUTPUT 參數(shù)的存儲過程不能通過數(shù)據(jù)庫 API 調(diào)用。只有將 cursor OUTPUT 變量賦值給 Transact-SQL 局部 cursor 變量時，才可以通過 Transact-SQL 批處理、存儲過程或觸發(fā)器調(diào)用這些過程。

Cursor 輸出參數(shù)
在執(zhí)行過程時，以下規(guī)則適用于 cursor 輸出參數(shù)：

對于只進(jìn)游標(biāo)，游標(biāo)的結(jié)果集中返回的行只是那些存儲過程執(zhí)行結(jié)束時處于或超出游標(biāo)位置的行，例如：
在過程中的名為 RS 的 100 行結(jié)果集上打開一個非滾動游標(biāo)。

過程提取結(jié)果集 RS 的頭 5 行。

過程返回到其調(diào)用者。

返回到調(diào)用者的結(jié)果集 RS 由 RS 的第 6 到 100 行組成，調(diào)用者中的游標(biāo)處于 RS 的第一行之前。
對于只進(jìn)游標(biāo)，如果存儲過程完成后，游標(biāo)位于第一行的前面，則整個結(jié)果集將返回給調(diào)用批處理、存儲過程或觸發(fā)器。返回時，游標(biāo)將位于第一行的前面。

對于只進(jìn)游標(biāo)，如果存儲過程完成后，游標(biāo)的位置超出最后一行的結(jié)尾，則為調(diào)用批處理、存儲過程或觸發(fā)器返回空結(jié)果集。

說明? 空結(jié)果集與空值不同。

對于可滾動游標(biāo)，在存儲過程執(zhí)行結(jié)束時，結(jié)果集中的所有行均會返回給調(diào)用批處理、存儲過程或觸發(fā)器。返回時，游標(biāo)保留在過程中最后一次執(zhí)行提取時的位置。

對于任意類型的游標(biāo)，如果游標(biāo)關(guān)閉，則將空值傳遞回調(diào)用批處理、存儲過程或觸發(fā)器。如果將游標(biāo)指派給一個參數(shù)，但該游標(biāo)從未打開過，也會出現(xiàn)這種情況。

說明? 關(guān)閉狀態(tài)只有在返回時才有影響。例如，可以在過程中關(guān)閉游標(biāo)，稍后再打開游標(biāo)，然后將該游標(biāo)的結(jié)果集返回給調(diào)用批處理、存儲過程或觸發(fā)器。

臨時存儲過程
SQL Server 支持兩種臨時過程：局部臨時過程和全局臨時過程。局部臨時過程只能由創(chuàng)建該過程的連接使用。全局臨時過程則可由所有連接使用。局部臨時過程在當(dāng)前會話結(jié)束時自動除去。全局臨時過程在使用該過程的最后一個會話結(jié)束時除去。通常是在創(chuàng)建該過程的會話結(jié)束時。

臨時過程用 # 和 ## 命名，可以由任何用戶創(chuàng)建。創(chuàng)建過程后，局部過程的所有者是唯一可以使用該過程的用戶。執(zhí)行局部臨時過程的權(quán)限不能授予其他用戶。如果創(chuàng)建了全局臨時過程，則所有用戶均可以訪問該過程，權(quán)限不能顯式廢除。只有在 tempdb 數(shù)據(jù)庫中具有顯式 CREATE PROCEDURE 權(quán)限的用戶，才可以在該數(shù)據(jù)庫中顯式創(chuàng)建臨時過程（不使用編號符命名）。可以授予或廢除這些過程中的權(quán)限。

說明? 頻繁使用臨時存儲過程會在 tempdb 中的系統(tǒng)表上產(chǎn)生爭用，從而對性能產(chǎn)生負(fù)面影響。建議使用 sp_executesql 代替。sp_executesql 不在系統(tǒng)表中存儲數(shù)據(jù)，因此可以避免這一問題。

自動執(zhí)行存儲過程
SQL Server 啟動時可以自動執(zhí)行一個或多個存儲過程。這些存儲過程必須由系統(tǒng)管理員創(chuàng)建，并在 sysadmin 固定服務(wù)器角色下作為后臺過程執(zhí)行。這些過程不能有任何輸入?yún)?shù)。

對啟動過程的數(shù)目沒有限制，但是要注意，每個啟動過程在執(zhí)行時都會占用一個連接。如果必須在啟動時執(zhí)行多個過程，但不需要并行執(zhí)行，則可以指定一個過程作為啟動過程，讓該過程調(diào)用其它過程。這樣就只占用一個連接。

在啟動時恢復(fù)了最后一個數(shù)據(jù)庫后，即開始執(zhí)行存儲過程。若要跳過這些存儲過程的執(zhí)行，請將啟動參數(shù)指定為跟蹤標(biāo)記 4022。如果以最低配置啟動 SQL Server（使用 -f 標(biāo)記），則啟動存儲過程也不會執(zhí)行。有關(guān)更多信息，請參見跟蹤標(biāo)記。

若要創(chuàng)建啟動存儲過程，必須作為 sysadmin 固定服務(wù)器角色的成員登錄，并在 master 數(shù)據(jù)庫中創(chuàng)建存儲過程。

使用 sp_procoption 可以：

將現(xiàn)有存儲過程指定為啟動過程。

停止在 SQL Server 啟動時執(zhí)行過程。

查看 SQL Server 啟動時執(zhí)行的所有過程的列表。
存儲過程嵌套
存儲過程可以嵌套，即一個存儲過程可以調(diào)用另一個存儲過程。在被調(diào)用過程開始執(zhí)行時，嵌套級將增加，在被調(diào)用過程執(zhí)行結(jié)束后，嵌套級將減少。如果超出最大的嵌套級，會使整個調(diào)用過程鏈?zhǔn)　？捎?@@NESTLEVEL 函數(shù)返回當(dāng)前的嵌套級。

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

性能監(jiān)視器對象名 性能監(jiān)視計數(shù)器名稱
SQLServer：緩沖區(qū)管理器 高速緩存大小（頁面數(shù)）
SQLServer：高速緩存管理器 高速緩存命中率
? 高速緩存頁
? 高速緩存對象計數(shù)*

* 各種分類的高速緩存對象均可以使用這些計數(shù)器，包括特殊 sql、準(zhǔn)備 sql、過程、觸發(fā)器等。

有關(guān)更多信息，請參見 SQL Server:Buffer Manager 對象和 SQL Server:Cache Manager 對象。

sql_statement 限制
除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 之外（這兩個語句必須是批處理中僅有的語句），任何 SET 語句均可以在存儲過程內(nèi)部指定。所選擇的 SET 選項在存儲過程執(zhí)行過程中有效，之后恢復(fù)為原來的設(shè)置。

如果其他用戶要使用某個存儲過程，那么在該存儲過程內(nèi)部，一些語句使用的對象名必須使用對象所有者的名稱限定。這些語句包括：

ALTER TABLE

CREATE INDEX

CREATE TABLE

所有 DBCC 語句

DROP TABLE

DROP INDEX

TRUNCATE TABLE

UPDATE STATISTICS
權(quán)限
CREATE PROCEDURE 的權(quán)限默認(rèn)授予 sysadmin 固定服務(wù)器角色成員和 db_owner 和 db_ddladmin 固定數(shù)據(jù)庫角色成員。sysadmin 固定服務(wù)器角色成員和 db_owner 固定數(shù)據(jù)庫角色成員可以將 CREATE PROCEDURE 權(quán)限轉(zhuǎn)讓給其他用戶。執(zhí)行存儲過程的權(quán)限授予過程的所有者，該所有者可以為其它數(shù)據(jù)庫用戶設(shè)置執(zhí)行權(quán)限。

示例
A. 使用帶有復(fù)雜 SELECT 語句的簡單過程
下面的存儲過程從四個表的聯(lián)接中返回所有作者（提供了姓名）、出版的書籍以及出版社。該存儲過程不使用任何參數(shù)。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
???????? WHERE name = 'au_info_all' AND type = 'P')
?? DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
?? FROM authors a INNER JOIN titleauthor ta
????? ON a.au_id = ta.au_id INNER JOIN titles t
????? ON t.title_id = ta.title_id INNER JOIN publishers p
????? ON t.pub_id = p.pub_id
GO

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

EXECUTE au_info_all
-- Or
EXEC au_info_all

如果該過程是批處理中的第一條語句，則可使用：

au_info_all

B. 使用帶有參數(shù)的簡單過程
下面的存儲過程從四個表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該存儲過程接受與傳遞的參數(shù)精確匹配的值。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
???????? WHERE name = 'au_info' AND type = 'P')
?? DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
?? @lastname varchar(40),
?? @firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
?? FROM authors a INNER JOIN titleauthor ta
????? ON a.au_id = ta.au_id INNER JOIN titles t
????? ON t.title_id = ta.title_id INNER JOIN publishers p
????? ON t.pub_id = p.pub_id
?? WHERE? au_fname = @firstname
????? AND au_lname = @lastname
GO

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

EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- Or
EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

如果該過程是批處理中的第一條語句，則可使用：

au_info 'Dull', 'Ann'
-- Or
au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
au_info @firstname = 'Ann', @lastname = 'Dull'

C. 使用帶有通配符參數(shù)的簡單過程
下面的存儲過程從四個表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該存儲過程對傳遞的參數(shù)進(jìn)行模式匹配，如果沒有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'au_info2' AND type = 'P')
?? DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
?? @lastname varchar(30) = 'D%',
?? @firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
?? ON a.au_id = ta.au_id INNER JOIN titles t
?? ON t.title_id = ta.title_id INNER JOIN publishers p
?? ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
?? AND au_lname LIKE @lastname
GO

au_info2 存儲過程可以用多種組合執(zhí)行。下面只列出了部分組合：

EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'
-- Or
EXECUTE au_info2 @firstname = 'A%'
-- Or
EXECUTE au_info2 '[CK]ars[OE]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'

D. 使用 OUTPUT 參數(shù)
OUTPUT 參數(shù)允許外部過程、批處理或多條 Transact-SQL 語句訪問在過程執(zhí)行期間設(shè)置的某個值。下面的示例創(chuàng)建一個存儲過程 (titles_sum)，并使用一個可選的輸入?yún)?shù)和一個輸出參數(shù)。

首先，創(chuàng)建過程：

USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
????? WHERE name = 'titles_sum' AND type = 'P')
?? DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO

接下來，將該 OUTPUT 參數(shù)用于控制流語言。

說明? OUTPUT 變量必須在創(chuàng)建表和使用該變量時都進(jìn)行定義。

參數(shù)名和變量名不一定要匹配，不過數(shù)據(jù)類型和參數(shù)位置必須匹配（除非使用 @@SUM = variable 形式）。

DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
?? PRINT ' '
?? PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
?? SELECT 'The total cost of these titles is $'
???????? + RTRIM(CAST(@@TOTALCOST AS varchar(20)))

下面是結(jié)果集：

Title Name??????????????????????????????????????????????????????????????
------------------------------------------------------------------------
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking

(3 row(s) affected)

Warning, null value eliminated from aggregate.
?
All of these titles can be purchased for less than $200.

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

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

USE pubs
IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles

OPEN @titles_cursor
GO

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

USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
?? FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

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

USE pubs
IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'titles_by_author' AND type = 'P')
?? DROP PROCEDURE titles_by_author
GO
CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '%'
WITH RECOMPILE
AS
SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name',
?? title AS Title
FROM authors a INNER JOIN titleauthor ta
?? ON a.au_id = ta.au_id INNER JOIN titles t
?? ON ta.title_id = t.title_id
WHERE au_lname LIKE @@LNAME_PATTERN
GO

G. 使用 WITH ENCRYPTION 選項
WITH ENCRYPTION 子句對用戶隱藏存儲過程的文本。下例創(chuàng)建加密過程，使用 sp_helptext 系統(tǒng)存儲過程獲取關(guān)于加密過程的信息，然后嘗試直接從 syscomments 表中獲取關(guān)于該過程的信息。

IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'encrypt_this' AND type = 'P')
?? DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO

EXEC sp_helptext encrypt_this

下面是結(jié)果集：

The object's comments have been encrypted.

接下來，選擇加密存儲過程內(nèi)容的標(biāo)識號和文本。

SELECT c.id, c.text
FROM syscomments c INNER JOIN sysobjects o
?? ON c.id = o.id
WHERE o.name = 'encrypt_this'

下面是結(jié)果集：

說明? text 列的輸出顯示在單獨一行中。執(zhí)行時，該信息將與 id 列信息出現(xiàn)在同一行中。

id???????? text???????????????????????????????????????????????????????
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????

(1 row(s) affected)

H. 創(chuàng)建用戶定義的系統(tǒng)存儲過程
下面的示例創(chuàng)建一個過程，顯示表名以 emp 開頭的所有表及其對應(yīng)的索引。如果沒有指定參數(shù)，該過程將返回表名以 sys 開頭的所有表（及索引）。

IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'sp_showindexes' AND type = 'P')
?? DROP PROCEDURE sp_showindexes
GO
USE master
GO
CREATE PROCEDURE sp_showindexes
?? @@TABLE varchar(30) = 'sys%'
AS
SELECT o.name AS TABLE_NAME,
?? i.name AS INDEX_NAME,
?? indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects o
?? ON o.id = i.id
WHERE o.name LIKE @@TABLE
GO????????
USE pubs
EXEC sp_showindexes 'emp%'
GO

下面是結(jié)果集：

TABLE_NAME?????? INDEX_NAME?????? INDEX_ID
---------------- ---------------- ----------------
employee???????? employee_ind???? 1
employee???????? PK_emp_id??????? 2

(2 row(s) affected)

I. 使用延遲名稱解析
下面的示例顯示四個過程以及延遲名稱解析的各種可能使用方式。盡管引用的表或列在編譯時不存在，但每個存儲過程都可創(chuàng)建。

IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'proc1' AND type = 'P')
?? DROP PROCEDURE proc1
GO
-- Creating a procedure on a nonexistent table.
USE pubs
GO
CREATE PROCEDURE proc1
AS
?? SELECT *
?? FROM does_not_exist
GO?
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
?? ON o.id = c.id
WHERE o.type = 'P' AND o.name = 'proc1'
GO
USE master
GO
IF EXISTS (SELECT name FROM sysobjects
????? WHERE name = 'proc2' AND type = 'P')
?? DROP PROCEDURE proc2
GO
-- Creating a procedure that attempts to retrieve information from a
-- nonexistent column in an existing table.
USE pubs
GO
CREATE PROCEDURE proc2
AS
?? DECLARE @middle_init char(1)
?? SET @middle_init = NULL
?? SELECT au_id, middle_initial = @middle_init
?? FROM authors
GO?
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
?? ON o.id = c.id
WHERE o.type = 'P' and o.name = 'proc2'