Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

ALTER PROCEDURE

SQL Server 2000

  Topic last updated -- June 2007

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 will convert the original text of the stored procedure to an obfuscated format. Note that obfuscated stored procedures can be reverse engineered because SQL Server must de-obfuscate procedures for execution. In SQL Server 2000, the obfuscated text is visible in the syscomments system table and may be susceptible to de-obfuscation attempts.

Using ENCRYPTION prevents the procedure from being published as part of SQL Server replication.

Note  During an upgrade, SQL Server uses the obfuscated comments stored in syscomments to re-create obfuscated 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

See Also

Data Types

DROP PROCEDURE

EXECUTE

Programming Stored Procedures

System Tables

Using Identifiers

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft