meta data for this page
CREATE PROCEDURE
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 */
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 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:
select * from ( select 'Procedure', rdb$procedure_name, rdb$valid_blr from rdb$procedures union select 'Trigger', rdb$trigger_name, rdb$valid_blr from rdb$triggers ) (type, name, valid) where exists (select * from rdb$dependencies where rdb$dependent_name = name and rdb$depended_on_name = 'MYDOMAIN') /* Replace MYDOMAIN with the actual domain name. Use all-caps if the domain was created case-insensitively. Otherwise, use the exact capitalisation. */
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 PROCEDURE PROCNAME and SHOW TRIGGER TRIGNAME, 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.
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