SQL Server存储过程及其创建

存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。存储过程是SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。

存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

可以出于任何使用SQL 语句的目的来使用存储过程，它具有以下优点：

?可以在单个存储过程中执行一系列SQL 语句。

?可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

?存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL 语句快。

存储过程的功能取决于数据库所提供的功能。

创建存储过程

可使用Transact-SQL 语句CREATE PROCEDURE 创建存储过程。创建存储过程前，请考虑下列事项：

?不能将CREATE PROCEDURE 语句与其它SQL 语句组合到单个批处理中。

?创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。

?存储过程是数据库对象，其名称必须遵守标识符规则。

?只能在当前数据库中创建存储过程。

创建存储过程时，应指定：

?所有输入参数和向调用过程或批处理返回的输出参数。

?执行数据库操作（包括调用其它过程）的编程语句。

?返回至调用过程或批处理以表明成功或失败（以及失败原因）的状态值。

系统存储过程

Microsoft? SQL Server? 2000 中的许多管理活动是通过一种称为系统存储过程的特殊过程执行的。系统存储过程在master数据库中创建并存储，带有sp_前缀。可从任何数据库中执行系统存储过程，而无需使用master 数据库名称来完全限定该存储过程的名称。

强烈建议您不要创建以sp_ 为前缀的存储过程。SQL Server 始终按照下列顺序查找以sp_ 开头的存储过程：

1.在master数据库中查找存储过程。

2.根据所提供的任何限定符（数据库名称或所有者）查找该存储过程。

3.如果未指定所有者，则使用dbo作为所有者查找该存储过程。

因此，虽然当前数据库中可能存在带sp_前缀的用户创建的存储过程，但总会先检查m aster数据库（即使该存储过程已用数据库名称限定）。

重要如果用户创建的存储过程与系统存储过程同名，则永远不执行用户创建的存储过程。

分组

如果将一个不同的标识号赋予某过程，则可以用与现有某存储过程相同的名称创建该过程，这样可允许将这些过程进行逻辑分组。同名的分组过程可以同时删除。在同一应用程序中使用的过程一般都以该方式分组。例如，用于my_app 应用程序的过程可能被命名为my _proc;1、my_proc;2 等。删除my_proc即删除该整个组。将过程分组后，就无法删除该组内的单个过程。

临时存储过程

专用和全局临时存储过程与临时表类似，都可以用向该过程名称添加# 和## 前缀的方法进行创建。# 表示本地临时存储过程，## 表示全局临时存储过程。SQL Server 关闭后，这些过程将不再存在。

临时存储过程在连接到SQL Server 的早期版本时很有用，这些早期版本不支持再次使用Transact-SQL 语句或批处理执行计划。连接到SQL Server 2000 的应用程序应使用sp_executesql系统存储过程，而不使用临时存储过程。有关更多信息，请参见执行计划的高速缓存和重新使用。

只有创建本地临时过程的连接才能执行该过程，当该连接关闭（用户从SQL Server 中注销）时，将自动删除该过程。

任何连接都可执行全局临时存储过程。只有创建该过程的用户所用的连接关闭，并且所有其它连接所用的该过程的当前执行版本运行完毕后，全局临时存储过程才不再存在。一旦用于创建该过程的连接关闭，将不再允许启动执行该全局临时存储过程。只允许那些已启动执行该存储过程的连接完成该过程的运行。

如果直接在tempdb数据库中创建没有# 或## 前缀的存储过程，则由于每次启动SQL Server 时tempdb都要重新创建，因此当关闭SQL Server 时将自动删除该存储过程。直接在tempdb中创建的过程即使在创建该过程的连接终止后也会存在。与任何其它对象一样，可向其他用户授予、拒绝和废除执行该临时存储过程的权限。

CREATE PROCEDURE

创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的Transact-SQL 语句的集合。

可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。

也可以创建在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 ]

参数

procedure_name

新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。有关更多信息，请参见使用标识符。

要创建局部临时过程，可以在procedure_name前面加一个编号符(#procedure_na me)，要创建全局临时过程，可以在procedure_name前面加两个编号符(##proced ure_name)。完整的名称（包括# 或##）不能超过128 个字符。指定过程所有者的名称是可选的。

;number

是可选的整数，用来对同名的过程分组，以便用一条DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为orders 的应用程序使用的过程可以命名为orderproc;

1、orderproc;2 等。DROP PROCEDURE orderproc语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在procedure_name前后使用适当的定界符。

@parameter

过程中的参数。在CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有2.100 个参数。

使用@ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见EX ECUTE。

data_type

参数的数据类型。所有数据类型（包括text、ntext和image）均可以用作存储过程的参数。不过，cursor数据类型只能用于OUTPUT 参数。如果指定的数据类型为cur sor，也必须同时指定VARYING 和OUTPUT 关键字。有关SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。

说明对于可以是cursor数据类型的输出参数，没有最大数目的限制。

VARYING

指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。

default

参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或NULL。如果过程将对该参数使用LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和[^]）。

OUTPUT

表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT 参数可将信息返回给调用过程。Text、ntext和image参数可用作OUTPUT 参数。使用OUTP UT 关键字的输出参数可以是游标占位符。

表示最多可以指定2.100 个参数的占位符。

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE 表明SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用RECOMPILE 选项。

ENCRYPTION 表示SQL Server 加密syscomments表中包含CREATE PROCE DURE 语句文本的条目。使用ENCRYPTION 可防止将过程作为SQL Server 复制的一部分发布。

说明在升级过程中，SQL Server 利用存储在syscomments 中的加密注释来重新创建加密过程。

FOR REPLICATION

指定不能在订阅服务器上执行为复制创建的存储过程。.使用FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和WITH REC OMPILE 选项一起使用。

指定过程要执行的操作。

sql_statement

过程中要包含的任意数目和类型的Transact-SQL 语句。但有一些限制。

是表示此过程可以包含多条Transact-SQL 语句的占位符。

注释

存储过程的最大大小为128 MB。

用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在tempdb 中创建）。在单个批处理中，CREATE PROCEDURE 语句不能与其它Transact-SQL 语句组合使用。

默认情况下，参数可为空。如果传递NULL 参数值并且该参数在CREATE 或ALTER T ABLE 语句中使用，而该语句中引用的列又不允许使用NULL，则SQL Server 会产生一条错误信息。为了防止向不允许使用NULL 的列传递NULL 参数值，应向过程中添加编程逻辑或为该列使用默认值（使用CREATE 或ALTER TABLE 的DEFAULT 关键字)。

建议在存储过程的任何CREATE TABLE 或ALTER TABLE 语句中都为每列显式指定NULL 或NOT NULL，例如在创建临时表时。ANSI_DFLT_ON 和ANSI_DFLT_OFF 选项控制SQL Server 为列指派NULL 或NOT NULL 特性的方式（如果在CREATE TABLE 或ALTER TABLE 语句中没有指定的话）。如果某个连接执行的存储过程对这些

选项的设置与创建该过程的连接的设置不同，则为第二个连接创建的表列可能会有不同的为空性，并且表现出不同的行为方式。如果为每个列显式声明了NULL 或NOT NULL，那么将对所有执行该存储过程的连接使用相同的为空性创建临时表。

在创建或更改存储过程时，SQL Server 将保存SET QUOTED_IDENTIFIER 和SET ANSI_NULLS 的设置。执行存储过程时，将使用这些原始设置。因此，所有客户端会话的SET QUOTED_IDENTIFIER 和SET ANSI_NULLS 设置在执行存储过程时都将被忽略。在存储过程中出现的SET QUOTED_IDENTIFIER 和SET ANSI_NULLS 语句不影响存储过程的功能。

其它SET 选项（例如SET ARITHABORT、SET ANSI_WARNINGS 或SET ANSI_ PADDINGS）在创建或更改存储过程时不保存。如果存储过程的逻辑取决于特定的设置，应在过程开头添加一条SET 语句，以确保设置正确。从存储过程中执行SET 语句时，该设置只在存储过程完成之前有效。之后，设置将恢复为调用存储过程时的值。这使个别的客户端可以设置所需的选项，而不会影响存储过程的逻辑。

说明 SQL Server 是将空字符串解释为单个空格还是解释为真正的空字符串，由兼容级别设置控制。如果兼容级别小于或等于65，SQL Server 就将空字符串解释为单个空格。如果兼容级别等于70，则SQL Server 将空字符串解释为空字符串。有关更多信息，请参见sp_dbcmptlevel。

获得有关存储过程的信息

若要显示用来创建过程的文本，请在过程所在的数据库中执行sp_helptext，并使用过程名作为参数。

说明使用ENCRYPTION 选项创建的存储过程不能使用sp_helptext查看。

若要显示有关过程引用的对象的报表，请使用sp_depends。

若要为过程重命名，请使用sp_rename。

引用对象

SQL Server 允许创建的存储过程引用尚不存在的对象。在创建时，只进行语法检查。执行时，如果高速缓存中尚无有效的计划，则编译存储过程以生成执行计划。只有在编译过程中才解析存储过程中引用的所有对象。因此，如果语法正确的存储过程引用了不存在的对象，则仍可以成功创建，但在运行时将失败，因为所引用的对象不存在。有关更多信息，请参见延迟名称解析和编译。

延迟名称解析和兼容级别

SQL Server 允许Transact-SQL 存储过程在创建时引用不存在的表。这种能力称为延迟名称解析。不过，如果Transact-SQL 存储过程引用了该存储过程中定义的表，而兼容级别设置（通过执行sp_dbcmptlevel来设置）为65，则在创建时会发出警告信息。而如果在运行时所引用的表不存在，将返回错误信息。有关更多信息，请参见sp_dbcmp tlevel和延迟名称解析和编译。

执行存储过程

成功执行CREATE PROCEDURE 语句后，过程名称将存储在sysobjects 系统表中，而CREATE PROCEDURE 语句的文本将存储在syscomments中。第一次执行时，将编译该过程以确定检索数据的最佳访问计划。

使用cursor 数据类型的参数

存储过程只能将cursor数据类型用于OUTPUT 参数。如果为某个参数指定了cursor 数据类型，也必须指定VARYING 和OUTPUT 参数。如果为某个参数指定了VARYIN G 关键字，则数据类型必须是cursor，并且必须指定OUTPUT 关键字。

说明cursor数据类型不能通过数据库API（例如OLE DB、ODBC、ADO 和DB -Library）绑定到应用程序变量上。因为必须先绑定OUTPUT 参数，应用程序才可以执行存储过程，所以带有cursor OUTPUT 参数的存储过程不能通过数据库API 调用。只有将cursor OUTPUT 变量赋值给Transact-SQL 局部cursor变量时，才可以通过Transact-SQL 批处理、存储过程或触发器调用这些过程。

Cursor 输出参数

在执行过程时，以下规则适用于cursor输出参数：

?对于只进游标，游标的结果集中返回的行只是那些存储过程执行结束时处于或超出游标位置的行，例如：

?在过程中的名为RS 的100 行结果集上打开一个非滚动游标。

?过程提取结果集RS 的头5 行。

?过程返回到其调用者。

?返回到调用者的结果集RS 由RS 的第6 到100 行组成，调用者中的游标处于RS 的第一行之前。

?对于只进游标，如果存储过程完成后，游标位于第一行的前面，则整个结果集将返回给调用批处理、存储过程或触发器。返回时，游标将位于第一行的前面。

?对于只进游标，如果存储过程完成后，游标的位置超出最后一行的结尾，则为调用批处理、存储过程或触发器返回空结果集。

说明空结果集与空值不同。

?对于可滚动游标，在存储过程执行结束时，结果集中的所有行均会返回给调用批处理、存储过程或触发器。返回时，游标保留在过程中最后一次执行提取时的位置。

?对于任意类型的游标，如果游标关闭，则将空值传递回调用批处理、存储过程或触发器。如果将游标指派给一个参数，但该游标从未打开过，也会出现这种情况。

说明关闭状态只有在返回时才有影响。例如，可以在过程中关闭游标，稍后再打开游标，然后将该游标的结果集返回给调用批处理、存储过程或触发器。

临时存储过程

SQL Server 支持两种临时过程：局部临时过程和全局临时过程。局部临时过程只能由创建该过程的连接使用。全局临时过程则可由所有连接使用。局部临时过程在当前会话结束时自动除去。全局临时过程在使用该过程的最后一个会话结束时除去。通常是在创建该过程的会话结束时。

临时过程用# 和## 命名，可以由任何用户创建。创建过程后，局部过程的所有者是唯一可以使用该过程的用户。执行局部临时过程的权限不能授予其他用户。如果创建了全局临时过程，则所有用户均可以访问该过程，权限不能显式废除。只有在tempdb数据库中具有显式CREATE PROCEDURE 权限的用户，才可以在该数据库中显式创建临时过程（不使用编号符命名）。可以授予或废除这些过程中的权限。

说明频繁使用临时存储过程会在tempdb中的系统表上产生争用，从而对性能产生负面影响。建议使用sp_executesql代替。sp_executesql不在系统表中存储数据，因此可以避免这一问题。

自动执行存储过程

SQL Server 启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员创建，并在sysadmin固定服务器角色下作为后台过程执行。这些过程不能有任何输入参数。

对启动过程的数目没有限制，但是要注意，每个启动过程在执行时都会占用一个连接。如果必须在启动时执行多个过程，但不需要并行执行，则可以指定一个过程作为启动过程，让该过程调用其它过程。这样就只占用一个连接。

在启动时恢复了最后一个数据库后，即开始执行存储过程。若要跳过这些存储过程的执行，请将启动参数指定为跟踪标记4022。如果以最低配置启动SQL Server（使用-f标记），则启动存储过程也不会执行。有关更多信息，请参见跟踪标记。

若要创建启动存储过程，必须作为sysadmin固定服务器角色的成员登录，并在mast er 数据库中创建存储过程。

使用sp_procoption 可以：

?将现有存储过程指定为启动过程。

?停止在SQL Server 启动时执行过程。

?查看SQL Server 启动时执行的所有过程的列表。

存储过程嵌套

存储过程可以嵌套，即一个存储过程可以调用另一个存储过程。在被调用过程开始执行时，嵌套级将增加，在被调用过程执行结束后，嵌套级将减少。如果超出最大的嵌套级，会使整个调用过程链失败。可用@@NESTLEVEL 函数返回当前的嵌套级。

若要估计编译后的存储过程大小，请使用下列性能监视计数器。

* 各种分类的高速缓存对象均可以使用这些计数器，包括特殊sql、准备sql、过程、触发器等。

有关更多信息，请参见SQL Server:Buffer Manager 对象和SQL Server:Cache Ma nager 对象。

sql_statement 限制

除了SET SHOWPLAN_TEXT 和SET SHOWPLAN_ALL 之外（这两个语句必须是批处理中仅有的语句），任何SET 语句均可以在存储过程内部指定。所选择的SET 选项在存储过程执行过程中有效，之后恢复为原来的设置。

如果其他用户要使用某个存储过程，那么在该存储过程内部，一些语句使用的对象名必须使用对象所有者的名称限定。这些语句包括：

?ALTER TABLE

?CREATE INDEX

?CREATE TABLE

?所有DBCC 语句

?DROP TABLE

?DROP INDEX

?TRUNCATE TABLE

?UPDATE STATISTICS

权限

CREATE PROCEDURE 的权限默认授予sysadmin固定服务器角色成员和db_own er和db_ddladmin固定数据库角色成员。sysadmin 固定服务器角色成员和db_ owner 固定数据库角色成员可以将CREATE PROCEDURE 权限转让给其他用户。执行存储过程的权限授予过程的所有者，该所有者可以为其它数据库用户设置执行权限。

示例

A. 使用带有复杂SELECT 语句的简单过程

下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info_all' AND type = 'P')

DROP PROCEDURE au_info_all

CREATE PROCEDURE au_info_all

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

au_info_all存储过程可以通过以下方法执行：

EXECUTE au_info_all

-- Or

EXEC au_info_all

如果该过程是批处理中的第一条语句，则可使用：

au_info_all

B. 使用带有参数的简单过程

下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info' AND type = 'P')

DROP PROCEDURE au_info

USE pubs

CREATE PROCEDURE au_info

@lastname varchar(40),

@firstname varchar(20)

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

au_info存储过程可以通过以下方法执行：

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. 使用带有通配符参数的简单过程

下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值。

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info2' AND type = 'P')

DROP PROCEDURE au_info2

USE pubs

CREATE PROCEDURE au_info2

@lastname varchar(30) = 'D%',

@firstname varchar(18) = '%'

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

au_info2存储过程可以用多种组合执行。下面只列出了部分组合：

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 参数

OUTPUT 参数允许外部过程、批处理或多条Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程(titles_sum)，并使用一个可选的输入参数和一个输出参数。

首先，创建过程：

USE pubs

IF EXISTS(SELECT name FROM sysobjects

WHERE name = 'titles_sum' AND type = 'P')

DROP PROCEDURE titles_sum

USE pubs

CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OU TPUT

SELECT 'Title Name' = title

FROM titles

WHERE title LIKE @@TITLE

SELECT @@SUM = SUM(price)

FROM titles

WHERE title LIKE @@TITLE

接下来，将该OUTPUT 参数用于控制流语言。

说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。

参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用@@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)))

下面是结果集：

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 游标参数

OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。

首先，创建以下过程，在titles 表上声明并打开一个游标：

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'titles_cursor' and type = 'P')

DROP PROCEDURE titles_cursor

CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT AS

SET @titles_cursor = CURSOR

FORWARD_ONLY STATIC FOR

SELECT *

FROM titles

OPEN @titles_cursor

接下来，执行一个批处理，声明一个局部游标变量，执行上述过程以将游标赋值给局部变量，然后从该游标提取行。

USE pubs

DECLARE @MyCursor CURSOR

EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT

WHILE (@@FETCH_STATUS = 0)

BEGIN

FETCH NEXT FROM @MyCursor

END

CLOSE @MyCursor

DEALLOCATE @MyCursor

F. 使用WITH RECOMPILE 选项

如果为过程提供的参数不是典型的参数，并且新的执行计划不应高速缓存或存储在内存中，WITH RECOMPILE 子句会很有帮助。

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'titles_by_author' AND type = 'P')

DROP PROCEDURE titles_by_author

CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '%' WITH RECOMPILE

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

G. 使用WITH ENCRYPTION 选项

WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程，使用sp_h elptext系统存储过程获取关于加密过程的信息，然后尝试直接从syscomments表中获取关于该过程的信息。

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'encrypt_this' AND type = 'P')

DROP PROCEDURE encrypt_this

USE pubs

CREATE PROCEDURE encrypt_this

WITH ENCRYPTION

SELECT *

FROM authors

EXEC sp_helptext encrypt_this

下面是结果集：

The object's comments have been encrypted.

接下来，选择加密存储过程内容的标识号和文本。

SELECT c.id, c.text

FROM syscomments c INNER JOIN sysobjects o

ON c.id = o.id

WHERE https://www.360docs.net/doc/473689374.html, = 'encrypt_this'

下面是结果集：

说明text 列的输出显示在单独一行中。执行时，该信息将与id 列信息出现在同一行中。

id text

---------- ------------------------------------------------------------

1413580074 ????????????????????????????????? e?????????????????????????????????????????

(1 row(s) affected)

H. 创建用户定义的系统存储过程

下面的示例创建一个过程，显示表名以emp开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以sys开头的所有表（及索引）。

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'sp_showindexes' AND type = 'P')

DROP PROCEDURE sp_showindexes

USE master

CREATE PROCEDURE sp_showindexes

@@TABLE varchar(30) = 'sys%'

SELECT https://www.360docs.net/doc/473689374.html, AS TABLE_NAME,

https://www.360docs.net/doc/473689374.html, AS INDEX_NAME,

indid AS INDEX_ID

FROM sysindexes i INNER JOIN sysobjects o

ON o.id = i.id

WHERE https://www.360docs.net/doc/473689374.html, LIKE @@TABLE

USE pubs

EXEC sp_showindexes 'emp%'

下面是结果集：

TABLE_NAME INDEX_NAME INDEX_ID

---------------- ---------------- ----------------

employee employee_ind 1

employee PK_emp_id 2