sql server 2005 T-SQL ALTER PROCEDURE (Transact-SQL)

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

Transact-SQL 语法约定

 语法

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

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

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

<method_specifier> ::=
EXTERNAL NAME 
assembly_name.class_name.method_name

 参数

schema_name

过程所属架构的名称。

procedure_name

要更改的过程的名称。过程名称必须符合标识符规则。

; number

现有的可选整数，该整数用来对具有同一名称的过程进行分组，以便可以用一个 DROP PROCEDURE 语句全部删除它们。

注意： 后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。

@ parameter

过程中的参数。最多可以指定 2,100 个参数。

[ type_schema_name. ] data_type

参数及其所属架构的数据类型。

有关数据类型限制的信息，请参阅 CREATE PROCEDURE (Transact-SQL)。

VARYING

指定作为输出参数支持的结果集。此参数由存储过程动态构造，并且其内容可以不同。仅适用于游标参数。

default

参数的默认值。

OUTPUT

指示参数是返回参数。

RECOMPILE

指示 SQL Server 2005 数据库引擎不会缓存该过程的计划，该过程在运行时重新编译。

ENCRYPTION

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

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

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

注意： 在升级过程中，数据库引擎使用存储在 sys.sql_modules 中的模糊注释来重新创建过程。

EXECUTE AS

指定访问存储过程后执行该存储过程所用的安全上下文。

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

FOR REPLICATION

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

AS

过程将要执行的操作。

<sql_statement>

过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。有关详细信息，请参阅 CREATE PROCEDURE (Transact-SQL) 中的“<sql_statement> 限制”。

EXTERNAL NAME , assembly_name.class_name.method_name

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

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

 备注

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

有关详细信息，请参阅 CREATE PROCEDURE (Transact-SQL) 中的“备注”部分。

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

 权限

要求对过程具有 ALTER 权限。

示例

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

复制代码

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

下面的示例将 uspVendorAllInfo 存储过程（不带 EXECUTE AS 选项）更改为只返回那些提供指定产品的供应商。LEFT 和 CASE 函数自定义结果集的外观。

复制代码

ALTER PROCEDURE Purchasing.uspVendorAllInfo
    @Product varchar(25) 
AS
    SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name', 
    'Credit 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.VendorID = pv.VendorID 
    INNER JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID 
    WHERE p.Name LIKE @Product
    ORDER BY v.Name ASC;
GO
EXEC Purchasing.uspVendorAllInfo N'LL Crankarm';
GO

下面是结果集：

复制代码

Vendor               Product name        Credit rating Availability
-------------------- ------------------- ------------- ------------
Proseware, Inc.      LL Crankarm         Average       No
Vision Cycles, Inc.  LL Crankarm         Superior      Yes

(2 row(s) affected)