ALTER PROCEDURE (T-SQL)

Alters a previously created procedure (created by executing the CREATE PROCEDURE statement) without changing permissions and without affecting any dependent stored procedures or triggers. For more information about the parameters used in the ALTER PROCEDURE statement, CREATE PROCEDURE.

Syntax

ALTER PROC[EDURE] procedure_name [;number]
    [
        {@parameter data_type } [VARYING] [= default] [OUTPUT]
    ]
        [,...n]
[WITH
    {
        RECOMPILE
        | ENCRYPTION
        | RECOMPILE , ENCRYPTION
    }
]
[FOR REPLICATION]
AS
    sql_statement [...n]

Arguments

procedure_name

Is the name of the procedure to change. Procedure names must conform to the rules for identifiers.

;number

Is an existing optional integer used to group procedures of the same name so that they can be dropped together with a single DROP PROCEDURE statement.

@parameter

Is a parameter in the procedure.

data_type

Is the data type of the parameter.

VARYING

Specifies the result set supported as an output parameter (constructed dynamically by the stored procedure and whose contents can vary). Applies only to cursor parameters.

default

Is a default value for the parameter.

OUTPUT

Indicates that the parameter is a return parameter.

n

Is a placeholder indicating up to 1,024 parameters can be specified.

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE indicates that Microsoft® SQL Server™ does not cache a plan for this procedure and the procedure is recompiled each time it is executed.

ENCRYPTION indicates that SQL Server encrypts the syscomments table entry that contains the text of the ALTER PROCEDURE statement.

---

Note During an upgrade, SQL Server uses the encrypted comments stored in syscomments to re-create encrypted procedures.

---

FOR REPLICATION

Specifies that stored procedures created for replication cannot be executed on the subscribing server, and is used when creating a filter stored procedure that is executed only by replication. This option cannot be used with the WITH RECOMPILE option.

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.

n

Is a placeholder indicating that multiple Transact-SQL statements can be included in the procedure. For more information, see sql_statement Limitations in CREATE PROCEDURE.

Remarks

For more information about ALTER PROCEDURE, see Remarks in CREATE PROCEDURE.

Note If a previous procedure definition was created using WITH ENCRYPTION or WITH RECOMPILE, these options are only enabled if they are included in ALTER PROCEDURE.

Permissions

ALTER PROCEDURE permissions default to members of the db_owner and ddl_admin fixed database roles, and are not transferable. Permissions and the startup property remain unchanged for a procedure modified with ALTER PROCEDURE.

Examples

This example creates a procedure called Oakland_authors that by default contains all authors from the city of Oakland, CA. Permissions are granted. Then, when the procedure must be changed to retrieve all authors from California, ALTER PROCEDURE is used to redefine the stored procedure.

USE pubs

GO

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'Oakland_authors' AND type = 'P')

    DROP PROCEDURE Oakland_authors

GO

-- Create a procedure from the authors table that contains author

-- information for those authors who live in Oakland, California.

USE pubs

GO

CREATE PROCEDURE Oakland_authors

AS

SELECT au_fname, au_lname, address, city, zip

FROM pubs..authors

WHERE city = 'Oakland'

and state = 'CA'

ORDER BY au_lname, au_fname

GO

-- Here is the statement to actually see the text of the procedure.

SELECT o.id, c.text

FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id

WHERE o.type = 'P' and o.name = 'Oakland_authors'

-- Here, EXECUTE permissions are granted on the procedure to public.

GRANT EXECUTE ON Oakland_authors TO public

GO

-- The procedure must be changed to include all

-- authors from California, regardless of what city they live in.

-- If ALTER PROCEDURE is not used but the procedure is dropped

-- and then re-created, the above GRANT statement and any

-- other statements dealing with permissions that pertain to this

-- procedure must be redone.

ALTER PROCEDURE Oakland_authors

WITH ENCRYPTION

AS

SELECT au_fname, au_lname, address, city, zip

FROM pubs..authors

WHERE state = 'CA'

ORDER BY au_lname, au_fname

GO

-- Here is the statement to actually see the text of the procedure.

SELECT o.id, c.text

FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id

WHERE o.type = 'P' and o.name = 'Oakland_authors'

GO

See Also