TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

ALTER PROCEDURE (Transact-SQL)

 

Updated: June 13, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Modifies a previously created procedure that was created by executing the CREATE PROCEDURE statement in SQL Server.

Topic link icon Transact-SQL Syntax Conventions (Transact-SQL)

-- Syntax for SQL Server  
  
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 Database  
   
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name   
    [ { @parameter [type_schema_name. ] data_type }   
    [ VARYING ] [= default ] [ [ OUT [ PUT ]   
    ] [,...n ]   
[ WITH <procedure_option> [ , ...n ] ]   
[ FOR REPLICATION ]
AS   
     { <sql_statement> [...n ] }  
[;]  
  
<procedure_option> ::=   
    [ ENCRYPTION ]  
    [ RECOMPILE ]   
    [ EXECUTE_AS_Clause ]   
  
<sql_statement> ::=   
{ [ BEGIN ] statements [ END ] }  

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

schema_name
The name of the schema to which the procedure belongs.

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

; number
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.

System_CAPS_ICON_note.jpg 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
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. This option is not valid for CLR procedures.

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 Database Engine does not cache a plan for this procedure and the procedure is recompiled at run time.

ENCRYPTION

Applies to: SQL Server (SQL Server 2008 through SQL Server 2016), SQL Database V12.

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

System_CAPS_ICON_note.jpg 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

Applies to: SQL Server (SQL Server 2008 through SQL Server 2016), SQL Database V12.

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. This option is not valid for CLR procedures. The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

System_CAPS_ICON_note.jpg Note


This option is not available in a contained database.

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
One or more Transact-SQL statements comprising the body of the procedure. You can use the optional BEGIN and END keywords to enclose the statements. 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_name

Applies to: SQL Server 2008 through SQL Server 2016, SQL Database V12 (Preview in some regions).

Specifies the method of a .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.

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.

System_CAPS_ICON_note.jpg Note


CLR procedures are not supported in a contained database.

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

ALTER PROCEDURE does not change permissions and does not affect any dependent stored procedures or triggers. 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.

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.

For more information about stored procedures, see CREATE PROCEDURE (Transact-SQL).

Permissions

Requires ALTER permission on the procedure or requires membership in the db_ddladmin fixed database role.

The following example creates the uspVendorAllInfo stored procedure. This procedure returns the names of all the vendors that supply Adventure 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  
  

The following example alters the uspVendorAllInfo stored procedure. It removes the EXECUTE AS CALLER clause and modifies the body of the procedure to return only those vendors that supply the specified product. 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)

CREATE PROCEDURE (Transact-SQL)
DROP PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL)
EXECUTE AS (Transact-SQL)
EVENTDATA (Transact-SQL)
Stored Procedures (Database Engine)
sys.procedures (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft