ALTER PROCEDURE (Transact-SQL)

Syntax

--Transact-SQL Stored Procedure 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 ]

--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 }
[;]

Arguments

• schema_name
  Is the name of the schema to which the procedure belongs.

• procedure_name
  Is the name of the procedure to change. Procedure names must comply with the rules for identifiers.

• **;**number
  Is 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.

  Note

  This feature will 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
  Is a parameter in the procedure. Up to 2,100 parameters can be specified.

• [ type_schema_name**.** ] data_type
  Is the data type of the parameter and the schema it belongs to.

  For information about data type restrictions, see CREATE PROCEDURE (Transact-SQL).

• VARYING
  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.

• default
  Is a default value for the parameter.

• OUT | OUTPUT
  Indicates that the parameter is a return parameter.

• READONLY
  Indicates that the parameter cannot be updated or modified within the body of the procedure. If the parameter type is a table-value type, READONLY must be specified.

• RECOMPILE
  Indicates that the SQL Server 2005 Database Engine does not cache a plan for this procedure and the procedure is recompiled at run time.

• ENCRYPTION
  Indicates that the Database Engine will convert the original text of the ALTER PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users that have no access to system tables or database files cannot retrieve the obfuscated text. 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.

  Procedures created with this option cannot be published as part of SQL Server replication.

  This option cannot be specified for common language runtime (CLR) stored procedures.

  Note

  During an upgrade, the Database Engine uses the obfuscated comments stored in sys.sql_modules to re-create procedures.

• EXECUTE AS
  Specifies the security context under which to execute the stored procedure after it is accessed.

  For more information, see EXECUTE AS Clause (Transact-SQL).

• FOR REPLICATION
  Specifies that stored procedures that are created for replication cannot be executed on the Subscriber. A stored procedure created with the FOR REPLICATION option is used as a stored procedure filter and only executed during replication. Parameters cannot be declared if FOR REPLICATION is specified. The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

• AS
  Are the actions the procedure is to take.

• <sql_statement>
  Is any number and type of Transact-SQL statements to be included in the procedure. Some limitations do apply. For more information, see "<sql_statement> Limitations" in CREATE PROCEDURE (Transact-SQL).

• EXTERNAL NAME assembly_name**.class_name.method_name
  Specifies the method of a Microsoft .NET Framework assembly for a CLR stored procedure to reference. class_name must be a valid SQL 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.

  Note

  By default, SQL Server cannot execute CLR code. You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL Server until you enable the clr enabled option. To enable the option, use sp_configure.

Remarks

Transact-SQL stored procedures cannot be modified to be CLR stored procedures and vice versa.

For more information, see the Remarks section in CREATE PROCEDURE (Transact-SQL).

Permissions

Requires ALTER permission on the procedure.

Examples

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
    SET NOCOUNT ON;
    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

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)

See Also

Tasks

Reference

Concepts

Other Resources