ALTER PROCEDURE

Alter an existing procedure.

 

Usage

Interactive, Embedded, Module, ODBC, JDBC.

Description

With the ALTER PROCEDURE statement it is possible to change attributes or the procedural sql statement used in the routine body for the procedure .

The procedure name should follow the normal rules for naming database objects, see Naming Objects.

If no schema name is given, it is assumed that the procedure is defined in a schema with the same name as the current ident.

If the procedure name is unique within the schema and only the routine attributes are altered, it is not necessary to provide a parameter list.

If there are multiple procedures with the same name, it is possible to identify the procedure by using a specific name or by providing a parameter list. How to use a specific name when altering a routine is described in the ALTER ROUTINE statement (see ALTER ROUTINE.)

The parameter-name should follow the normal rules for naming SQL identifiers, see SQL Identifiers.

The routine attributes that can be altered are: DETERMINISTIC, ACCESS MODE, IS NULL CALL and SPECIFIC. If a routine attribute is not present in the ALTER PROCEDURE statement the attribute will keep the value it had prior to the statement.

The meaning of the routine attributes are the same as when creating a procedure (see CREATE PROCEDURE.)

It is possible to change the data type in the returns clause, with some restrictions (see below).

If there is a returns clause and a procedural sql statement in the alter procedure statement, the procedure will be a result set procedure. Likewise if there is no returns clause and a procedural sql statement in the alter procedure statement, the procedure will be an regular procedure. Thus it is possible to change an regular procedure to a result set procedure and vice versa.

Restrictions

It is only the creator of the schema in which the procedure is defined, that is allowed to alter the procedure.

It is not possible to alter the data type of a parameter.

If the routine body is altered, a complete parameter list with names must also be given.

It is possible to change the data type in the returns clause if there are no other objects referencing this procedure or if the new data types are comparable with the old data type (see Comparisons.)

If the altered routine body contains references to objects on which the current ident does not have the applicable privilege with grant option and there are other objects referencing the procedure being altered, the alter operation is not allowed.

In addition, all restrictions for create procedure also applies.

Notes

Any privilege on the function granted to other idents will remain.

It is possible to alter a procedure that is part of a module.

Example

Alter the access mode for a procedure

CREATE PROCEDURE INSERT_AUTHOR

    (IN FIRST_NAME NCHAR VARYING(30),IN LAST_NAME NCHAR VARYING(30))

   INSERT INTO AUTHORS VALUES(FIRST_NAME,LAST_NAME)

 

ALTER PROCEDURE INSERT_AUTHOR modifies sql data

 

Example of altering the routine body

ALTER PROCEDURE INSERT_AUTHOR

    (IN FIRST_NAME NCHAR VARYING(30),IN LAST_NAME NCHAR VARYING(30))

BEGIN

    ...

END

Standard Compliance