ALTER PROCEDURE (Transact-SQL)ALTER PROCEDURE (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

修改先前在 SQL ServerSQL Server 中通过执行 CREATE PROCEDURE 语句创建的过程。Modifies a previously created procedure that was created by executing the CREATE PROCEDURE statement in SQL ServerSQL Server.

主题链接图标 Transact-SQL 语法约定 (Transact-SQL)Topic link icon Transact-SQL Syntax Conventions (Transact-SQL)

语法Syntax

-- Syntax for SQL Server and Azure SQL Database
  
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 ]  
-- Syntax for SQL Server CLR Stored Procedure  
  
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 }  
[;]  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name  
    [ { @parameterdata_type } [= ] ] [ ,...n ]  
AS { [ BEGIN ] sql_statement [ ; ] [ ,...n ] [ END ] }  
[;]  

参数Arguments

schema_nameschema_name
过程所属架构的名称。The name of the schema to which the procedure belongs.

procedure_name procedure_name
要更改的过程的名称。The name of the procedure to change. 过程名称必须符合 标识符规则。Procedure names must comply with the rules for identifiers.

; number ; number
现有的可选整数,该整数用来对具有同一名称的过程进行分组,以便可以用一个 DROP PROCEDURE 语句全部删除它们。An existing optional integer that is used to group procedures of the same name so that they can be dropped together by using one DROP PROCEDURE statement.

备注

此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

@ parameter @ parameter
过程中的参数。A parameter in the procedure. 最多可以指定 2,100 个参数。Up to 2,100 parameters can be specified.

[ type_schema_name .[ type_schema_name. ] data_type] data_type
参数及其所属架构的数据类型。Is the data type of the parameter and the schema it belongs to.

有关数据类型限制的信息,请参阅 CREATE PROCEDURE (Transact-SQL)For information about data type restrictions, see CREATE PROCEDURE (Transact-SQL).

VARYINGVARYING
指定作为输出参数支持的结果集。Specifies the result set supported as an output parameter. 此参数由存储过程动态构造,并且其内容可以不同。This parameter is constructed dynamically by the stored procedure and its contents can vary. 仅适用于游标参数。Applies only to cursor parameters. 该选项对于 CLR 过程无效。This option is not valid for CLR procedures.

default default
参数的默认值。Is a default value for the parameter.

OUT | OUTPUTOUT | OUTPUT
指示参数是返回参数。Indicates that the parameter is a return parameter.

READONLYREADONLY
指示不能在过程的主体中更新或修改参数。Indicates that the parameter cannot be updated or modified within the body of the procedure. 如果参数类型为表值类型,则必须指定 READONLY。If the parameter type is a table-value type, READONLY must be specified.

RECOMPILERECOMPILE
指示数据库引擎Database Engine不会缓存该过程的计划,该过程在运行时重新编译。Indicates that the 数据库引擎Database Engine does not cache a plan for this procedure and the procedure is recompiled at run time.

ENCRYPTIONENCRYPTION
适用对象:SQL Server(SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.

指示数据库引擎Database Engine会将 ALTER PROCEDURE 语句的原始文本转换为模糊格式。Indicates that the 数据库引擎Database Engine will convert the original text of the ALTER PROCEDURE statement to an obfuscated format. 模糊代码的输出在 SQL ServerSQL Server 的任何目录视图中都不能直接显示。The output of the obfuscation is not directly visible in any of the catalog views in SQL ServerSQL Server. 对系统表或数据库文件没有访问权限的用户不能检索模糊文本。Users that have no access to system tables or database files cannot retrieve the obfuscated text. 但是,可以通过 DAC 端口访问系统表的特权用户或直接访问数据库文件的特权用户可以使用此文本。However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. 此外,能够向服务器进程附加调试器的用户可在运行时从内存中检索原始过程。Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at runtime. 有关如何访问系统元数据的详细信息,请参阅元数据可见性配置For more information about accessing system metadata, see Metadata Visibility Configuration.

使用此选项创建的过程不能作为 SQL ServerSQL Server 复制的一部分发布。Procedures created with this option cannot be published as part of SQL ServerSQL Server replication.

不能为公共语言运行时 (CLR) 存储过程指定此选项。This option cannot be specified for common language runtime (CLR) stored procedures.

备注

在升级过程中,数据库引擎Database Engine 使用存储在 sys.sql_modules 中的模糊注释来重新创建过程 。During an upgrade, the 数据库引擎Database Engine uses the obfuscated comments stored in sys.sql_modules to re-create procedures.

EXECUTE ASEXECUTE AS
指定访问存储过程后执行该存储过程所用的安全上下文。Specifies the security context under which to execute the stored procedure after it is accessed.

有关详细信息,请参阅 EXECUTE AS 子句 (Transact-SQL)For more information, see EXECUTE AS Clause (Transact-SQL).

FOR REPLICATIONFOR REPLICATION

指定不能在订阅服务器上执行为复制创建的存储过程。Specifies that stored procedures that are created for replication cannot be executed on the Subscriber. 使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选,且只能在复制过程中执行。A stored procedure created with the FOR REPLICATION option is used as a stored procedure filter and only executed during replication. 如果指定了 FOR REPLICATION,则无法声明参数。Parameters cannot be declared if FOR REPLICATION is specified. 该选项对于 CLR 过程无效。This option is not valid for CLR procedures. 对于使用 FOR REPLICATION 创建的过程,忽略 RECOMPILE 选项。The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

备注

此选项在包含数据库中不可用。This option is not available in a contained database.

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
构成过程主体的一个或多个 Transact-SQLTransact-SQL 语句。One or more Transact-SQLTransact-SQL statements comprising the body of the procedure. 您可以使用可选的 BEGIN 和 END 关键字将这些语句括起来。You can use the optional BEGIN and END keywords to enclose the statements. 有关详细信息,请参阅 CREATE PROCEDURE (Transact-SQL) 中的“最佳做法”、“一般备注”以及“限制和局限”部分。For more information, see the Best Practices, General Remarks, and Limitations and Restrictions sections in CREATE PROCEDURE (Transact-SQL).

EXTERNAL NAME assembly_name . class_name . method_nameEXTERNAL NAME assembly_name.class_name.method_name
适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

指定 .NET Framework.NET Framework 程序集的方法,以便 CLR 存储过程引用。Specifies the method of a .NET Framework.NET Framework assembly for a CLR stored procedure to reference. class_name 必须是有效的 SQL ServerSQL Server 标识符,并且必须作为类存在于程序集中 。class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the assembly. 如果类具有使用句点 (.) 分隔命名空间部分的命名空间限定名称,则必须使用方括号 ([]) 或引号 ("") 来分隔类名 。If the class has a namespace-qualified name uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([]) or quotation marks (""). 指定的方法必须为该类的静态方法。The specified method must be a static method of the class.

默认情况下,SQL ServerSQL Server 不能执行 CLR 代码。By default, SQL ServerSQL Server cannot execute CLR code. 可以创建、修改和删除引用公共语言运行时模块的数据库对象;不过,只有在启用 clr enabled 选项之后,才能在 SQL ServerSQL Server 中执行这些引用。You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL ServerSQL Server until you enable the clr enabled option. 若要启用该选项,请使用 sp_configureTo enable the option, use sp_configure.

备注

包含数据库中不支持 CLR 过程。CLR procedures are not supported in a contained database.

一般备注General Remarks

Transact-SQLTransact-SQL 存储过程修改为 CLR 存储过程,反之亦然。stored procedures cannot be modified to be CLR stored procedures and vice versa.

ALTER PROCEDURE 不会更改权限,也不影响相关的存储过程或触发器。ALTER PROCEDURE does not change permissions and does not affect any dependent stored procedures or triggers. 但是,当修改存储过程时,QUOTED_IDENTIFIER 和 ANSI_NULLS 的当前会话设置包含在该存储过程中。However, the current session settings for QUOTED_IDENTIFIER and ANSI_NULLS are included in the stored procedure when it is modified. 如果设置不同于最初创建存储过程时有效的设置,则存储过程的行为可能会更改。If the settings are different from those in effect when stored procedure was originally created, the behavior of the stored procedure may change.

如果原来的过程定义是使用 WITH ENCRYPTION 或 WITH RECOMPILE 创建的,那么只有在 ALTER PROCEDURE 中也包含这些选项时,这些选项才有效。If a previous procedure definition was created using WITH ENCRYPTION or WITH RECOMPILE, these options are enabled only if they are included in ALTER PROCEDURE.

有关存储过程的详细信息,请参阅 CREATE PROCEDURE (Transact-SQL)For more information about stored procedures, see CREATE PROCEDURE (Transact-SQL).

SecuritySecurity

权限Permissions

要求对过程具有 ALTER 权限,或者要求 db_ddladmin 固定数据库角色中的成员身份 。Requires ALTER permission on the procedure or requires membership in the db_ddladmin fixed database role.

示例Examples

以下示例将创建 uspVendorAllInfo 存储过程。The following example creates the uspVendorAllInfo stored procedure. 此过程返回提供 Adventure Works CyclesAdventure Works Cycles 的所有供应商的名称、所提供的产品、信用等级以及可用性。This procedure returns the names of all the vendors that supply Adventure Works CyclesAdventure Works Cycles, the products they supply, their credit ratings, and their availability. 创建过程之后,便可修改过程以返回不同的结果集。After this procedure is created, it is then modified to return a different result set.

  
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 存储过程。The following example alters the uspVendorAllInfo stored procedure. 该示例将删除 EXECUTE AS CALLER 子句并且将过程的主体修改为只返回那些提供指定产品的供应商。It removes the EXECUTE AS CALLER clause and modifies the body of the procedure to return only those vendors that supply the specified product. LEFTCASE 函数自定义结果集的外观。The LEFT and CASE functions customize the appearance of the result set.

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  
  

下面是结果集:Here is the result set.

Vendor               Product name  Rating    Availability  
-------------------- ------------- -------   ------------  
Proseware, Inc.      LL Crankarm   Average   No  
Vision Cycles, Inc.  LL Crankarm   Superior  Yes  
(2 row(s) affected)`  

另请参阅See Also

CREATE PROCEDURE (Transact-SQL) CREATE PROCEDURE (Transact-SQL)
DROP PROCEDURE (Transact-SQL) DROP PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
EXECUTE AS (Transact-SQL) EXECUTE AS (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
存储过程(数据库引擎) Stored Procedures (Database Engine)
sys.procedures (Transact-SQL)sys.procedures (Transact-SQL)