ALTER PROCEDURE
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, see 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 2,100 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 at run time.
ENCRYPTION indicates that SQL Server encrypts the syscomments table entry that contains the text of the ALTER PROCEDURE statement. Using ENCRYPTION prevents the procedure from being published as part of SQL Server replication.
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 Subscriber. A stored procedure created with the FOR REPLICATION option is used as a stored procedure filter and only executed during 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 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 sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the owner of the procedure, 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, California. 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 re-entered.
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