FirebirdSQL logo
 TRIGGERFUNCTION 

Who Can Alter a Procedure

The ALTER PROCEDURE statement can be executed by:

  • Administrators

  • The owner of the stored procedure

  • Users with the ALTER ANY PROCEDURE privilege

ALTER PROCEDURE Example

Altering the GET_EMP_PROJ stored procedure.
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

CREATE OR ALTER PROCEDURE

Creates a stored procedure if it does not exist, or alters a stored procedure

Available in

DSQL

Syntax
CREATE OR ALTER PROCEDURE procname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]
  {<psql_procedure> | <external-module-body>}

!! See syntax of CREATE PROCEDURE for further rules !!

The CREATE OR ALTER PROCEDURE statement creates a new stored procedure or alters an existing one.If the stored procedure does not exist, it will be created by invoking a CREATE PROCEDURE statement transparently.If the procedure already exists, it will be altered and compiled without affecting its existing privileges and dependencies.

CREATE OR ALTER PROCEDURE Example

Creating or altering the GET_EMP_PROJ procedure.
CREATE OR 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