<< PROCEDURE | FB 2.5 Language Reference | ALTER PROCEDURE >>

CREATE PROCEDURE

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
 <declarations>   ::= See PSQL::DECLARE for the exact syntax

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

back to top of page

TYPE OF COLUMN in parameter and variable declarations

Changed 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.

back to top of page

Domains supported in parameter and variable declarations

Changed in: 2.1

Description

Firebird 2.1 and up allow 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.

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.

back to top of page

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

back to top of page

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

back to top of page

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

See also:
STORED PROCEDURE
Stored procedure and trigger language
Procedural SQL
Writing stored procedures and triggers

back to top of page
<< PROCEDURE | FB 2.5 Language Reference | ALTER PROCEDURE >>