FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 TRIGGERFUNCTION 

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
See also

[fblangref50-ddl-proc-create], [fblangref50-ddl-proc-alter], [fblangref50-ddl-proc-recreate]

docnext count = 5

DROP PROCEDURE

Drops a stored procedure

Available in

DSQL, ESQL

Syntax
DROP PROCEDURE procname
Table 1. DROP PROCEDURE Statement Parameter
Parameter Description

procname

Name of an existing stored procedure

The DROP PROCEDURE statement deletes an existing stored procedure.If the stored procedure has any dependencies, the attempt to delete it will fail and raise an error.

Who Can Drop a Procedure

The DROP PROCEDURE statement can be executed by:

  • Administrators

  • The owner of the stored procedure

  • Users with the DROP ANY PROCEDURE privilege

DROP PROCEDURE Example

Deleting the GET_EMP_PROJ stored procedure.
DROP PROCEDURE GET_EMP_PROJ;
See also

[fblangref50-ddl-proc-create], [fblangref50-ddl-proc-recreate]

RECREATE PROCEDURE

Drops a stored procedure if it exists, and creates a stored procedure

Available in

DSQL

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

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

The RECREATE PROCEDURE statement creates a new stored procedure or recreates an existing one.If a procedure with this name already exists, the engine will try to drop it and create a new one.Recreating an existing procedure will fail at the COMMIT request if the procedure has dependencies.

Warning

Be aware that dependency errors are not detected until the COMMIT phase of this operation.

After a procedure is successfully recreated, privileges to execute the stored procedure, and the privileges of the stored procedure itself are dropped.

RECREATE PROCEDURE Example

Creating the new GET_EMP_PROJ stored procedure or recreating the existing GET_EMP_PROJ stored procedure.
RECREATE 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
See also

[fblangref50-ddl-proc-create], [fblangref50-ddl-proc-drop], [fblangref50-ddl-proc-crtoralter]