ALTER PROCEDURE
Go Up to Statement and Function Reference (Language Reference Guide)
Changes the definition of an existing stored procedure. Available in DSQL and isql but not in the embedded language or in the stored procedures or triggers.
ALTER PROCEDURE'' ''<name> [(<param>'' ''data_type [,'' ''<param>'' ''data_type …])]
[RETURNS (<param>'' ''data_type [, <param> data_type …])]
AS ''procedure_body'' ;
| Argument | Description |
|---|---|
|
<name> |
Name of an existing procedure. |
|
<param data_type> |
Input parameters used by the procedure; legal data types are listed under |
|
|
Output parameters used by the procedure; legal data types are listed under |
|
<procedure_body> |
The procedure body includes:
See CREATE PROCEDURE for a complete description. |
Description: ALTER PROCEDURE changes an existing stored procedure without affecting its dependencies. It can modify the input parameters, output parameters, and body of a procedure.
The complete procedure header and body must be included in the ALTER PROCEDURE statement. The syntax is exactly the same as CREATE PROCEDURE, except CREATE is replaced by ALTER.
Be careful about changing the type, number, and order of input and output parameters to a procedure, because existing application code may assume the procedure has its original format. Check for dependencies between procedures before changing parameters. Should you change parameters and find that another procedure can neither be altered to accept the new parameters or deleted, change the original procedure back to its original parameters, fix the calling procedure, then change the called procedure.
A procedure can be altered by its creator, the SYSDBA user, and any users with operating system root privileges. Procedures in use are not altered until they are no longer in use. ALTER PROCEDURE changes take effect when they are committed. Changes are then reflected in all applications that use the procedure without recompiling or relinking.
Example: The following isql statements alter the GET_EMP_PROJ procedure, changing the return parameter to have a data type of VARCHAR(20):
ALTER PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (PROJ_ID VARCHAR(20)) AS
BEGIN
FOR SELECT PROJ_ID
FROM EMPLOYEE_PROJECT
WHERE EMP_NO = :emp_no
INTO :proj_id
DO
SUSPEND;
END;