5.8. PROCEDURE

A stored procedure is a software module that can be called from a client, another procedure, an executable block or a trigger. Stored procedures, executable blocks and triggers are written in procedural SQL (PSQL). Most SQL statements are available in PSQL as well, sometimes with limitations or extensions. Among notable exceptions are DDL and transaction control statements.

Stored procedures can have many input and output parameters.

5.8.1. CREATE PROCEDURE

Used forCreating a new stored procedure

Available inDSQL, ESQL

Syntax 

   |CREATE PROCEDURE procname
   |  [(<inparam> [, <inparam> ...])]
   |  [RETURNS (<outparam> [, <outparam> ...])]
   |AS
   |  [<declarations>]
   |BEGIN
   |  [<PSQL_statements>]
   |END
   | 
   |<inparam> ::= <param_decl> [{= | DEFAULT} <value>]
   | 
   |<outparam> ::= <param_decl>
   | 
   |<value> ::= {<literal> | NULL | <context_var>}
   | 
   |<param_decl> ::= paramname <type> [NOT NULL]
   |  [COLLATE collation]
   | 
   |<type> ::=
   |    <datatype>
   |  | [TYPE OF] domain
   |  | TYPE OF COLUMN rel.col
   | 
   |<datatype> ::=
   |    {SMALLINT | INT[EGER] | BIGINT}
   |  | {FLOAT | DOUBLE PRECISION}
   |  | {DATE | TIME | TIMESTAMP}
   |  | {DECIMAL | NUMERIC} [(precision [, scale])]
   |  | {CHAR | CHARACTER} [VARYING] | VARCHAR} [(size)]
   |    [CHARACTER SET charset]
   |  | {NCHAR | NATIONAL {CHARACTER | CHAR}} [VARYING]
   |    [(size)]
   |  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
   |    [SEGMENT SIZE seglen] [CHARACTER SET charset]
   |  | BLOB [(seglen [, subtype_num])]
   | 
   |<declarations> ::= {<declare_var> | <declare_cursor>};
   |  [{<declare_var> | <declare_cursor>}; …]

Table 5.24. CREATE PROCEDURE Statement Parameters
ParameterDescription

procname

Stored procedure name consisting of up to 31 characters. Must be unique among all table, view and procedure names in the database

inparam

Input parameter description

outparam

Output parameter description

declarations

Section for declaring local variables and named cursors

declare_var

Local variable declaration

declare_cursor

Named cursor declaration

PSQL_statements

Procedural SQL statements

literal

A literal value that is assignment-compatible with the data type of the parameter

context_var

Any context variable whose type is compatible with the data type of the parameter

paramname

The name of an input or output parameter of the procedure. It may consist of up to 31 characters. The name of the parameter must be unique among input and output parameters of the procedure and its local variables

datatype

SQL data type

collation

Collation sequence

domain

Domain name

rel

Table or view name

col

Table or view column name

precision

The total number of significant digits that the parameter should be able to hold (1..18)

scale

The number of digits after the decimal point (0..precision)

size

The maximum size of a string type parameter or variable, in characters

charset

Character set of a string type parameter or variable

subtype_num

BLOB subtype number

subtype_name

BLOB subtype mnemonic name

seglen

Segment size (max. 65535)

The CREATE PROCEDURE statement creates a new stored procedure. The name of the procedure must be unique among the names of all stored procedures, tables and views in the database.

CREATE PROCEDURE is a compound statement, consisting of a header and a body. The header specifies the name of the procedure and declares input parameters and the output parameters, if any, that are to be returned by the procedure.

The procedure body consists of declarations for any local variables and named cursors that will be used by the procedure, followed by one or more statements, or blocks of statements, all enclosed in an outer block that begins with the keyword BEGIN and ends with the keyword END. Declarations and embedded statements are terminated with semi-colons (;).

5.8.1.1. Statement Terminators

Some SQL statement editors — specifically the isql utility that comes with Firebird and possibly some third-party editors — employ an internal convention that requires all statements to be terminated with a semi-colon. This creates a conflict with PSQL syntax when coding in these environments. If you are unacquainted with this problem and its solution, please study the details in the PSQL chapter in the section entitled Switching the Terminator in isql.

5.8.1.2. Parameters

Each parameter has a data type specified for it. The NOT NULL constraint can also be specified for any parameter, to prevent NULL being passed or assigned to it.

A collation sequence can be specified for string-type parameters, using the COLLATE clause.

Input Parameters

Input parameters are presented as a parenthesized list following the name of the procedure. They are passed into the procedure as values, so anything that changes them inside the procedure has no effect on the parameters in the calling program. Input parameters may have default values. Those that do have values specified for them must be located at the end of the list of parameters.

Output Parameters

The optional RETURNS clause is for specifying a parenthesised list of output parameters for the stored procedure.

5.8.1.2.1. Use of Domains in Declarations

A domain name can be specified as the type of a parameter. The parameter will inherit all domain attributes. If a default value is specified for the parameter, it overrides the default value specified in the domain definition.

If the TYPE OF clause is added before the domain name, only the data type of the domain is used: any of the other attributes of the domain — NOT NULL constraint, CHECK constraints, default value — are neither checked nor used. However, if the domain is of a text type, its character set and collation sequence are always used.

5.8.1.2.2. Use of Column Type in Declarations

Input and output parameters can also be declared using the data type of columns in existing tables and views. The TYPE OF COLUMN clause is used for that, specifying relationname.columnname as its argument.

When TYPE OF COLUMN is used, the parameter inherits only the data type and — for string types — the character set and collation sequence. The constraints and default value of the column are ignored.

🛑
Bug warning for pre-Firebird 3 versions:

For input parameters, the collation that comes with the column’s type is ignored in comparisons (e.g. equality tests). For local variables, the behaviour varies.

The bug was fixed for Firebird 3.

5.8.1.3. Variable and Cursor Declarations

The optional declarations section, located last in the header section of the procedure definition, defines variables local to the procedure and its named cursors. Local variable declarations follow the same rules as parameters regarding specification of the data type. See details in the PSQL chapter for DECLARE VARIABLE and DECLARE CURSOR.

5.8.1.4. Procedure Body

The header section is followed by the procedure body, consisting of one or more PSQL statements enclosed between the outer keywords BEGIN and END. Multiple BEGIN …​ END blocks of terminated statements may be embedded inside the procedure body.

Any user connected to the database can create a new stored procedure. The user who creates a stored procedure becomes its owner.

5.8.1.5. Examples

Creating a stored procedure that inserts a record into the BREED table and returns the code of the inserted record:

   |CREATE PROCEDURE ADD_BREED (
   |  NAME D_BREEDNAME, /* Domain attributes are inherited */
   |  NAME_EN TYPE OF D_BREEDNAME, /* Only the domain type is inherited */
   |  SHORTNAME TYPE OF COLUMN BREED.SHORTNAME,
   |    /* The table column type is inherited */
   |  REMARK VARCHAR(120) CHARACTER SET WIN1251 COLLATE PXW_CYRL,
   |  CODE_ANIMAL INT NOT NULL DEFAULT 1
   |)
   |RETURNS (
   |  CODE_BREED INT
   |)
   |AS
   |BEGIN
   |  INSERT INTO BREED (
   |    CODE_ANIMAL, NAME, NAME_EN, SHORTNAME, REMARK)
   |  VALUES (
   |    :CODE_ANIMAL, :NAME, :NAME_EN, :SHORTNAME, :REMARK)
   |  RETURNING CODE_BREED INTO CODE_BREED;
   |END

Creating a selectable stored procedure that generates data for mailing labels (from employee.fdb):

   |CREATE PROCEDURE mail_label (cust_no INTEGER)
   |RETURNS (line1 CHAR(40), line2 CHAR(40), line3 CHAR(40),
   |         line4 CHAR(40), line5 CHAR(40), line6 CHAR(40))
   |AS
   |  DECLARE VARIABLE customer VARCHAR(25);
   |  DECLARE VARIABLE first_name VARCHAR(15);
   |  DECLARE VARIABLE last_name VARCHAR(20);
   |  DECLARE VARIABLE addr1 VARCHAR(30);
   |  DECLARE VARIABLE addr2 VARCHAR(30);
   |  DECLARE VARIABLE city VARCHAR(25);
   |  DECLARE VARIABLE state VARCHAR(15);
   |  DECLARE VARIABLE country VARCHAR(15);
   |  DECLARE VARIABLE postcode VARCHAR(12);
   |  DECLARE VARIABLE cnt INTEGER;
   |BEGIN
   |  line1 = '';
   |  line2 = '';
   |  line3 = '';
   |  line4 = '';
   |  line5 = '';
   |  line6 = '';
   | 
   |  SELECT customer, contact_first, contact_last, address_line1,
   |    address_line2, city, state_province, country, postal_code
   |  FROM CUSTOMER
   |  WHERE cust_no = :cust_no
   |  INTO :customer, :first_name, :last_name, :addr1, :addr2,
   |    :city, :state, :country, :postcode;
   | 
   |  IF (customer IS NOT NULL) THEN
   |    line1 = customer;
   |  IF (first_name IS NOT NULL) THEN
   |    line2 = first_name || ' ' || last_name;
   |  ELSE
   |    line2 = last_name;
   |  IF (addr1 IS NOT NULL) THEN
   |    line3 = addr1;
   |  IF (addr2 IS NOT NULL) THEN
   |    line4 = addr2;
   | 
   |  IF (country = 'USA') THEN
   |  BEGIN
   |    IF (city IS NOT NULL) THEN
   |  	  line5 = city || ', ' || state || '  ' || postcode;
   |  	ELSE
   |      line5 = state || '  ' || postcode;
   |  END
   |  ELSE
   |  BEGIN
   |    IF (city IS NOT NULL) THEN
   |  	  line5 = city || ', ' || state;
   |  	ELSE
   |      line5 = state;
   |    line6 = country || '    ' || postcode;
   |  END
   | 
   |  SUSPEND; -- the statement that sends an output row to the buffer
   |           -- and makes the procedure "selectable"
   |END

See alsoSection 5.8.3, “CREATE OR ALTER PROCEDURE”, Section 5.8.2, “ALTER PROCEDURE”, Section 5.8.5, “RECREATE PROCEDURE”, Section 5.8.4, “DROP PROCEDURE”

5.8.2. ALTER PROCEDURE

Used forModifying an existing stored procedure

Available inDSQL, ESQL

Syntax 

   |ALTER 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> ::=
   |    <datatype>
   |  | [TYPE OF] domain
   |  | TYPE OF COLUMN rel.col
   | 
   |<datatype> ::=
   |    {SMALLINT | INT[EGER] | BIGINT}
   |  | {FLOAT | DOUBLE PRECISSION}
   |  | {DATE | TIME | TIMESTAMP}
   |  | {DECIMAL | NUMERIC} [(precision [, scale])]
   |  | {CHAR | CHARACTER} [VARYING] | VARCHAR} [(size)]
   |    [CHARACTER SET charset]
   |  | {NCHAR | NATIONAL {CHARACTER | CHAR} [VARYING]
   |    [(size)]
   |  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
   |    [SEGMENT SIZE seglen] [CHARACTER SET charset]
   |  | BLOB [(seglen [, subtype_num])]
   | 
   |<declarations> ::= {<declare_var> | <declare_cursor>};
   |  [{<declare_var> | <declare_cursor>}; …]

Table 5.25. ALTER PROCEDURE Statement Parameters
ParameterDescription

procname

Name of an existing stored procedure

inparam

Input parameter description

outparam

Output parameter description

declarations

Section for declaring local variables and named cursors

declare_var

Local variable declaration

declare_cursor

Named cursor declaration

PSQL_statements

Procedural SQL statements

literal

A literal value that is assignment-compatible with the data type of the parameter

context_var

Any context variable whose type is compatible with the data type of the parameter

paramname

The name of an input or output parameter of the procedure. It may consist of up to 31 characters. The name of the parameter must be unique among input and output parameters of the procedure and its local variables

datatype

SQL data type

collation

Collation sequence

domain

Domain name

rel

Table or view name

col

Table or view column name

precision

The total number of significant digits that the parameter should be able to hold (1..18)

scale

The number of digits after the decimal point (0..precision)

size

The maximum size of a string type parameter or variable, in characters

charset

Character set of a string type parameter or variable

subtype_num

BLOB subtype number

subtype_name

BLOB subtype mnemonic name

seglen

Segment size (max. 65535)

The ALTER PROCEDURE statement allows the following changes to a stored procedure definition:

  • the set and characteristics of input and output parameters

  • local variables

  • code in the body of the stored procedure

After ALTER PROCEDURE executes, existing privileges remain intact and dependencies are not affected.

⚠
Caution

Take care about changing the number and type of input and output parameters in stored procedures. Existing application code and procedures and triggers that call it could become invalid because the new description of the parameters is incompatible with the old calling format. For information on how to troubleshoot such a situation, see the article The RDB$VALID_BLR Field in the Appendix.

The procedure owner and Administrators have the authority to use ALTER PROCEDURE.

ALTER PROCEDURE ExampleAltering 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

See alsoSection 5.8.1, “CREATE PROCEDURE”, Section 5.8.3, “CREATE OR ALTER PROCEDURE”, Section 5.8.5, “RECREATE PROCEDURE”, Section 5.8.4, “DROP PROCEDURE”

5.8.3. CREATE OR ALTER PROCEDURE

Used forCreating a new stored procedure or altering an existing one

Available inDSQL

Syntax 

  |CREATE OR ALTER PROCEDURE procname
  |  [(<inparam> [, <inparam> ...])]
  |  [RETURNS (<outparam> [, <outparam> ...])]
  |AS
  |  [<declarations>]
  |BEGIN
  |  [<PSQL_statements>]
  |END

For the full syntax detail, see Section 5.8.1, “CREATE PROCEDURE”.

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.

ExampleCreating 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 alsoSection 5.8.1, “CREATE PROCEDURE”, Section 5.8.2, “ALTER PROCEDURE”, Section 5.8.5, “RECREATE PROCEDURE”

5.8.4. DROP PROCEDURE

Used forDeleting a stored procedure

Available inDSQL, ESQL

Syntax 

  |DROP PROCEDURE procname

Table 5.26. DROP PROCEDURE Statement Parameter
ParameterDescription

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 the appropriate error will be raised.

The procedure owner and Administrators have the authority to use DROP PROCEDURE.

ExampleDeleting the GET_EMP_PROJ stored procedure.

  |DROP PROCEDURE GET_EMP_PROJ;

See alsoSection 5.8.1, “CREATE PROCEDURE”, Section 5.8.5, “RECREATE PROCEDURE”

5.8.5. RECREATE PROCEDURE

Used forCreating a new stored procedure or recreating an existing one

Available inDSQL

Syntax 

  |RECREATE PROCEDURE procname
  |  [(<inparam> [, <inparam> ...])]
  |  [RETURNS (<outparam> [, <outparam> ...])]
  |AS
  |  [<declarations>]
  |BEGIN
  |  [<PSQL_statements>]
  |END

For the full syntax detail, see Section 5.8.1, “CREATE PROCEDURE”.

The RECREATE PROCEDURE statement creates a new stored procedure or recreates an existing one. If there is a procedure with this name already, the engine will try to delete 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.

ExampleCreating 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 alsoSection 5.8.1, “CREATE PROCEDURE”, Section 5.8.4, “DROP PROCEDURE”, Section 5.8.3, “CREATE OR ALTER PROCEDURE”