ALTER PROCEDURE (Transact-SQL)

项目
03/07/2014

修改先前在 SQL Server 中通过执行 CREATE PROCEDURE 语句创建的过程。

适用范围：SQL Server（SQL Server 2008 至当前版本），Windows Azure SQL Database（初始版本至当前版本）。

语法

--SQL Server Syntax
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 ]

--SQL Server CLR Stored Procedure Syntax
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

-- Windows Azure SQL Database Syntax 

ALTER { PROC | PROCEDURE } [schema_name.] procedure_name 
    [ { @parameter [type_schema_name. ] data_type } 
    [ VARYING ] [= default ] [ [ OUT [ PUT ] 
    ] [,...n ] 
[ WITH <procedure_option> [ , ...n ] ] 
AS 
     { <sql_statement> [...n ] }
[;]

<procedure_option> ::= 
    [ RECOMPILE ] 
    [ EXECUTE_AS_Clause ] 

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

参数

schema_name
过程所属架构的名称。
procedure_name
要更改的过程的名称。过程名称必须符合标识符规则。
; number
现有的可选整数，该整数用来对具有同一名称的过程进行分组，以便可以用一个 DROP PROCEDURE 语句全部删除它们。

备注

后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。
@ parameter
过程中的参数。最多可以指定 2,100 个参数。
[ type_schema_name**.**] data_type
参数及其所属架构的数据类型。

有关数据类型限制的信息，请参阅 CREATE PROCEDURE (Transact-SQL)。
VARYING
指定作为输出参数支持的结果集。此参数由存储过程动态构造，并且其内容可以不同。仅适用于游标参数。该选项对于 CLR 过程无效。
default
参数的默认值。
OUT | OUTPUT
指示参数是返回参数。
READONLY
指示不能在过程的主体中更新或修改参数。如果参数类型为表值类型，则必须指定 READONLY。
RECOMPILE
指示数据库引擎不会缓存该过程的计划，该过程在运行时重新编译。
ENCRYPTION

适用范围：SQL Server 2008 到 SQL Server 2014。

指示数据库引擎会将 ALTER PROCEDURE 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是，可以通过 DAC 端口访问系统表的特权用户或直接访问数据文件的特权用户可以使用此文本。此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索原始过程。有关访问系统元数据的详细信息，请参阅元数据可见性配置。

使用此选项创建的过程不能作为 SQL Server 复制的一部分发布。

不能为公共语言运行时 (CLR) 存储过程指定此选项。

备注

在升级过程中，数据库引擎使用存储在 sys.sql_modules 中的模糊注释来重新创建过程。
EXECUTE AS
指定访问存储过程后执行该存储过程所用的安全上下文。

有关详细信息，请参阅 EXECUTE AS 子句 (Transact-SQL)。
FOR REPLICATION

适用范围：SQL Server 2008 到 SQL Server 2014。

指定不能在订阅服务器上执行为复制创建的存储过程。使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。如果指定了 FOR REPLICATION，则无法声明参数。该选项对于 CLR 过程无效。对于使用 FOR REPLICATION 创建的过程，忽略 RECOMPILE 选项。

备注

此选项在包含数据库中不可用。
{ [ BEGIN ] sql_statement [;] [ ...n ][ END ] }
构成过程主体的一个或多个 Transact-SQL 语句。您可以使用可选的 BEGIN 和 END 关键字将这些语句括起来。有关详细信息，请参阅 CREATE PROCEDURE (Transact-SQL) 中的“最佳做法”、“一般备注”以及“限制和局限”部分。
EXTERNAL NAME assembly_name**.class_name.**method_name

适用范围：SQL Server 2008 到 SQL Server 2014。

指定 .NET Framework 程序集的方法，以便 CLR 存储过程引用。 class_name 必须为有效的 SQL Server 标识符，并且必须作为类存在于程序集中。如果类具有使用句点 (.) 分隔命名空间部分的命名空间限定名称，则必须使用方括号 ([ ]) 或引号 (" ") 来分隔类名。指定的方法必须为该类的静态方法。

默认情况下，SQL Server 不能执行 CLR 代码。可以创建、修改和删除引用公共语言运行时模块的数据库对象；不过，只有在启用 clr enabled 选项之后，才能在 SQL Server 中执行这些引用。若要启用该选项，请使用 sp_configure。

备注

包含数据库中不支持 CLR 过程。

一般备注

不能将 Transact-SQL 存储过程修改为 CLR 存储过程，反之亦然。

ALTER PROCEDURE 不会更改权限，也不影响相关的存储过程或触发器。但是，当修改存储过程时，QUOTED_IDENTIFIER 和 ANSI_NULLS 的当前会话设置包含在该存储过程中。如果设置不同于最初创建存储过程时有效的设置，则存储过程的行为可能会更改。

如果原来的过程定义是使用 WITH ENCRYPTION 或 WITH RECOMPILE 创建的，那么只有在 ALTER PROCEDURE 中也包含这些选项时，这些选项才有效。

有关存储过程的详细信息，请参阅 CREATE PROCEDURE (Transact-SQL)。

安全性

权限

要求对过程具有 ALTER 权限，或者要求 db_ddladmin 固定数据库角色中的成员身份。

示例

以下示例将创建 uspVendorAllInfo 存储过程。此过程返回提供 Adventure Works Cycles 的所有供应商的名称、所提供的产品、信用等级以及可用性。创建过程之后，便可修改过程以返回不同的结果集。

IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor, p.Name AS 'Product name', 
      v.CreditRating AS 'Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.BusinessEntityID = pv.BusinessEntityID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

以下示例将更改 uspVendorAllInfo 存储过程。该示例将删除 EXECUTE AS CALLER 子句并且将过程的主体修改为只返回那些提供指定产品的供应商。 LEFT 和 CASE 函数自定义结果集的外观。

USE AdventureWorks2012;
GO
ALTER PROCEDURE Purchasing.uspVendorAllInfo
    @Product varchar(25) 
AS
    SET NOCOUNT ON;
    SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name', 
    'Rating' = CASE v.CreditRating 
        WHEN 1 THEN 'Superior'
        WHEN 2 THEN 'Excellent'
        WHEN 3 THEN 'Above average'
        WHEN 4 THEN 'Average'
        WHEN 5 THEN 'Below average'
        ELSE 'No rating'
        END
    , Availability = CASE v.ActiveFlag
        WHEN 1 THEN 'Yes'
        ELSE 'No'
        END
    FROM Purchasing.Vendor AS v 
    INNER JOIN Purchasing.ProductVendor AS pv
      ON v.BusinessEntityID = pv.BusinessEntityID 
    INNER JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID 
    WHERE p.Name LIKE @Product
    ORDER BY v.Name ASC;
GO

下面是结果集：

Vendor Product name Rating Availability

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

Proseware, Inc. LL Crankarm Average No

Vision Cycles, Inc. LL Crankarm Superior Yes

(2 row(s) affected)