9.2.4 修改存储过程

当需要修改存储过程的时候，可以通过SQL Server Management Studio实现，也可以通过执行ALTER PROCEDURE语句来实现。

1. 通过使用SQL Server Management Studio修改存储过程

使用 SQL Server Management Studio，在对象资源管理器中修改存储过程。

(1) 在对象资源管理器中，连接到数据库引擎实例，然后展开该实例。

(2) 展开“数据库”、“过程所属的数据库”及“可编程性”。

(3) 展开“存储过程”，右键单击要修改的过程，再选择“修改”。

(4) 修改存储过程的文本，又分为以下几种。

①若要测试语法，请在“查询”菜单上选择“分析”。

②若要将修改信息保存到过程定义中，请在“查询”菜单上选择“执行”。

③若要将更新的过程定义另存为T-SQL脚本，请在“文件”菜单上选择“另存为”。接受该文件名或将其替换为新的名称，再选择“保存”。

修改存储过程student_grade，检查结果按照课程进行排序，如图9-29所示。

再次执行该存储过程，结果集按照课程进行排序，结果如图9-30所示。

2. 使用ALTER PROCEDURE语句修改存储过程

其语法格式如下：

ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]

[ {@parameter [ type_schema_name. ] data_type }

[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]

] [ ,…n ]

[ WITH ＜procedure_option＞ [ , …n ] ]

[ FOR REPLICATION ]

AS { [ BEGIN ] sql_statement [;] […n ] [ END ] }

[;]

＜procedure_option＞::=

[ ENCRYPTION ]

[ RECOMPILE ]

[ EXECUTE AS Clause ]

其参数的含义与CREATE PROCEDURE的类似，这里不再介绍。

【例9-23】修改student_grade1存储过程，增加参数，改为不缓存，编写程序。

解：程序如下。

ALTER PROCEDURE [dbo].[student_grade1]

with recompile

AS

BEGIN

SELECT s.sno,s.sname, cname,grade

FROM student s INNER JOIN enroll sc

ON s.sno = sc.sno INNER JOIN course c

ON c.cno= sc.cno

END

具体的执行结果如图9-31所示。

使用ALTER PROCEDURE语句时，需要考虑以下事项。

(1) 如果要修改具有任何选项的存储过程，如WITH ENCRYPTION选项，则必须在ALTER PROCEDURE语句中包括该选项，以保留该选项提供的功能。

(2) ALTER PROCEDURE语句只能修改一个单一的过程，如果过程调用了其他存储过程，则嵌套的存储过程不受影响。

(3) 默认状态下，允许该语句的执行者是存储过程最初的创建者、sysadmin服务器角色成员和 db_owner 与 db_ddladmin 固定的数据库角色成员，用户不能授权执行 ALTER PROCEDURE语句。

建议不要直接修改系统存储过程，相反，可以通过从现有的存储过程中复制语句来创建用户定义的系统存储过程，然后修改它来满足要求。