PROCEDURE

A stored procedure (SP) is a code module that can be called by the client, by another stored procedure or by a trigger. Stored
procedures and triggers are written in Procedural SQL (PSQL). Most SQL statements are also available in PSQL, sometimes with
restrictions or extensions. Notable exceptions are DDL and transaction control statements.

Domains supported in parameter and variable declarations

Changed in: 2.1

Description: Firebird 2.1 and up support the use of domains instead of SQL datatypes when declaring input/output parameters and local variables.
With the “TYPE OF” modifier only the domain's type is used, not its NOT NULL setting, CHECK constraint and/or default value.

Warning

If you change a domain's definition, existing PSQL code using that domain may become invalid. If this happens, the system
table field RDB$VALID_BLR will be set to 0 for any procedure or trigger whose code is no longer valid. If you have changed a domain, the following
query will find the code modules that depend on it and report the state of RDB$VALID_BLR:

Unfortunately, not all PSQL invalidations will be reflected in the RDB$VALID_BLR field. It is therefore advisable to look at all the procedures and triggers reported by the above query, even those having
a 1 in the “VALID” column.

Please notice that for PSQL modules inherited from earlier Firebird versions (including a number of system triggers, even
if the database was created under Firebird 2.1 or higher), RDB$VALID_BLR is NULL. This does not indicate that their BLR is invalid.

The isql commands SHOW PROCEDURES and SHOW TRIGGERS flag modules whose RDB$VALID_BLR field is zero with an asterisk. SHOW PROCEDUREPROCNAME and SHOW TRIGGERTRIGNAME, which display individual PSQL modules, do not signal invalid BLR.

COLLATE in variable and parameter declarations

Changed in: 2.1

Description: Firebird 2.1 and up allow COLLATE clauses in declarations of input/output parameters and local variables.

Important: If you provide a default value for a parameter, you must do the same for any and all parameters following it.

BEGIN ... END blocks may be empty

Changed in: 1.5

Description: BEGIN ... END blocks may be empty in Firebird 1.5 and up, allowing you to write stub code without having to resort to dummy statements.

Example:

create procedure grab_ints (a integer, b integer)
as
begin
end

ALTER PROCEDURE

Available in: DSQL, ESQL

Default argument values

Added in: 2.0

Description: You can now provide default values for stored procedure arguments, allowing the caller to omit one or more items from the
end of the argument list. See CREATE PROCEDURE for syntax and details.

COLLATE in variable and parameter declarations

Description: Firebird 2.1 and up allow COLLATE clauses in declarations of input/output parameters and local variables. See CREATE PROCEDURE for syntax and details.

Domains supported in parameter and variable declarations

Changed in: 2.1

Description: Firebird 2.1 and up support the use of domains instead of SQL datatypes when declaring input/output parameters and local variables.
See CREATE PROCEDURE for syntax and details.

NOT NULL in variable and parameter declarations

Changed in: 2.1

Description: Firebird 2.1 and up allow NOT NULL constraints in declarations of input/output parameters and local variables. See CREATE PROCEDURE for syntax and details.

Restriction on altering used procedures

Changed in: 2.0, 2.0.1

Description: In Firebird 2.0 only, a restriction is in place which prevents anyone from dropping, altering or recreating a trigger or stored
procedure if it has been used since the database was opened. This restriction has been removed again in version 2.0.1. Still,
performing these operations on a live database is potentially risky and should only be done with the utmost care.

CREATE OR ALTER PROCEDURE

Available in: DSQL

Added in: 1.5

Description: If the procedure does not yet exist, it is created just as if CREATE PROCEDURE were used. If it already exists, it is altered and recompiled. Existing permissions and dependencies are preserved.

Syntax: Exactly the same as for CREATE PROCEDURE.

DROP PROCEDURE

Available in: DSQL, ESQL

Restriction on dropping used procedures

Changed in: 2.0, 2.0.1

Description: In Firebird 2.0 only, a restriction is in place which prevents anyone from dropping, altering or recreating a trigger or stored
procedure if it has been used since the database was opened. This restriction has been removed again in version 2.0.1. Still,
performing these operations on a live database is potentially risky and should only be done with the utmost care.

RECREATE PROCEDURE

Available in: DSQL

Added in: 1.0

Description: Creates or recreates a stored procedure. If a procedure with the same name already exists, RECREATE PROCEDURE will try to drop it and create a new procedure. RECREATE PROCEDURE will fail if the existing SP is in use.

Restriction on recreating used procedures

Changed in: 2.0, 2.0.1

Description: In Firebird 2.0 only, a restriction is in place which prevents anyone from dropping, altering or recreating a trigger or stored
procedure if it has been used since the database was opened. This restriction has been removed again in version 2.0.1. Still,
performing these operations on a live database is potentially risky and should only be done with the utmost care.