PROCEDURE

Table of Contents

CREATE PROCEDURE
ALTER PROCEDURE
CREATE OR ALTER PROCEDURE
DROP PROCEDURE
RECREATE PROCEDURE

A stored procedure (SP) is a code module that can be called by the client, by another stored procedure, an executable block or a trigger. Stored procedures, executable blocks 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.

Stored procedures can accept and return multiple parameters.

CREATE PROCEDURE

Table of Contents

TYPE OF COLUMN in parameter and variable declarations
Domains supported in parameter and variable declarations
COLLATE in variable and parameter declarations
NOT NULL in variable and parameter declarations
Default argument values
BEGIN ... END blocks may be empty

Available in: DSQL, ESQL

Description: Creates a stored procedure.

Syntax:

CREATE PROCEDURE procname
   [(<inparam> [, <inparam> ...])]
   [RETURNS (<outparam> [, <outparam> ...])]
AS
   [<declarations>]
BEGIN
   [<PSQL statements>]
END

<inparam>       ::=  <param_decl> [{= | DEFAULT} value]
<outparam>      ::=  <param_decl>
<param_decl>    ::=  paramname <type> [NOT NULL] [COLLATE collation]
<type>          ::=  sql_datatype | [TYPE OF] domain | TYPE OF COLUMN rel.col
<declarations>  ::=  See PSQL::DECLARE for the exact syntax

/* If sql_datatype is a string type, it may include a character set */

TYPE OF COLUMN in parameter and variable declarations

Added in: 2.5

Description: Analogous to the “TYPE OF domain” syntax supported since version 2.1, it is now also possible to declare variables and parameters as having the type of an existing table or view column. Only the type itself is used; in the case of string types, this includes the character set and the collation. Constraints and default values are never copied from the source column.

Example:

/* Assuming DDL autocommit and connection charset UTF8 */

create domain dphrase as
  varchar(200) character set utf8 collate unicode_ci_ai;

create table phrases (phrase dphrase);

set term #;
create procedure equalphrases (a type of column phrases.phrase,
                               b type of column phrases.phrase)
  returns (res varchar(30))
as
begin
  if (a = b) then res = 'Yes'; else res = 'No';
  suspend;
end#
set term ;#

select res from equalphrases('Appel', 'appèl');

-- result is 'Yes'

Warnings

For text types, character set and collation are included by TYPE OF COLUMN – just as when [TYPE OF] <domain> is used. However, due to a bug, the collation is not always taken into consideration when comparisons (e.g. equality tests) are made. In cases where the collation is of importance, test your code thoroughly before deploying! This bug is fixed for Firebird 3.
If the column's type is changed at a later time, PSQL code using that column may become invalid. For information on how to detect this, please read the note The RDB$VALID_BLR field, near the end of this document.

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 data types 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. If the domain is of a text type, its character set and collation are always preserved.

Example:

create domain bool3
  smallint
  check (value is null or value in (0,1));

create domain bigposnum
  bigint
  check (value >= 0);

/* Determines if A is a multiple of B: */
set term #;
create procedure ismultiple (a bigposnum, b bigposnum)
  returns (res bool3)
as
  declare ratio type of bigposnum;      -- ratio is a bigint
  declare remainder type of bigposnum;  -- so is remainder
begin
  if (a is null or b is null) then res = null;
  else if (b = 0) then
  begin
    if (a = 0) then res = 1; else res = 0;
  end
  else
  begin
    ratio = a / b;                      -- integer division!
    remainder = a - b*ratio;
    if (remainder = 0) then res = 1; else res = 0;
  end
end#
set term ;#

Warning

If a domain's definition is changed, existing PSQL code using that domain may become invalid. For information on how to detect this, please read the note The RDB$VALID_BLR field, near the end of this document.

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.

Example:

create procedure SpanishToDutch
  (es_1 varchar(20) character set iso8859_1 collate es_es,
   es_2 my_char_domain collate es_es)
returns
  (nl_1 varchar(20) character set iso8859_1 collate du_nl,
   nl_2 my_char_domain collate du_nl)
as
declare s_temp varchar(100) character set utf8 collate unicode;
begin
  ...
  ...
end

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.

Example:

create procedure RegisterOrder
  (order_no int not null, description varchar(200) not null)
returns
  (ticket_no int not null)
as
declare temp int not null;
begin
  ...
  ...
end

Default argument values

Changed in: 2.0

Description: It is now possible to provide default values for stored procedure arguments, allowing the caller to omit one or more items (possibly even all) from the end of the argument list.

Syntax:

CREATE PROCEDURE procname (<inparam> [, <inparam> ...])
   ...

<inparam>  ::=  paramname datatype [{= | DEFAULT} value]
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

Table of Contents

Default argument values
Classic Server: Altered procedure immediately visible to other clients
COLLATE in variable and parameter declarations
Domains supported in parameter and variable declarations
NOT NULL in variable and parameter declarations
Restriction on altering used procedures
TYPE OF COLUMN in parameter and variable declarations

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.

Example:

alter procedure TestProc
  (a int, b int default 1007, s varchar(12) = '-')
  ...

Classic Server: Altered procedure immediately visible to other clients

Changed in: 2.5

Description: Traditionally, when a client used ALTER PROCEDURE on a Classic server, other clients would keep seeing (and possibly executing) the old version for the duration of their connection. This has been fixed in 2.5. Now, all clients see the new version as soon as the changes have been committed.

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. 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 data types 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.

TYPE OF COLUMN in parameter and variable declarations

Added in: 2.5

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

Table of Contents

Restriction on dropping used procedures

Available in: DSQL, ESQL

Restriction on dropping used procedures

Changed in: 2.0, 2.0.1

RECREATE PROCEDURE

Table of Contents

Restriction on recreating used procedures

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.

Syntax: Exactly the same as CREATE PROCEDURE.

Restriction on recreating used procedures

Changed in: 2.0, 2.0.1