The Firebird 5.0 Language Reference

A stored procedure is a software module that can be called from a client, another procedure, function, executable block or trigger.Stored procedures are written in procedural SQL (PSQL) or defined using a UDR (User-Defined Routine).Most SQL statements are available in PSQL as well, sometimes with limitations or extensions.Notable limitations are the prohibition on DDL and transaction control statements in PSQL.

Stored procedures can have many input and output parameters.

`CREATE PROCEDURE`

Creates a stored procedure

Available in

DSQL, ESQL

Syntax

CREATE PROCEDURE procname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]
  {<psql_procedure> | <external-module-body>}

<in_params> ::= <inparam> [, <inparam> ...]

<inparam> ::= <param_decl> [{= | DEFAULT} <value>]

<out_params> ::= <outparam> [, <outparam> ...]

<outparam> ::= <param_decl>

<value> ::= {<literal> | NULL | <context_var>}

<param_decl> ::= paramname <domain_or_non_array_type> [NOT NULL]
  [COLLATE collation]

<type> ::=
    <datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN rel.col

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<psql_procedure> ::=
  [SQL SECURITY {INVOKER | DEFINER}]
  <psql-module-body>

<psql-module-body> ::=
  !! See Syntax of Module Body !!

<external-module-body> ::=
  !! See Syntax of Module Body !!

Table 1. `CREATE PROCEDURE` Statement Parameters
Parameter	Description
procname	Stored procedure name.The maximum length is 63 characters.Must be unique among all table, view and procedure names in the database
inparam	Input parameter description
outparam	Output parameter description
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.The maximum length is 63 characters.The name of the parameter must be unique among input and output parameters of the procedure and its local variables
collation	Collation

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, named cursors, and subroutines 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 semicolons (‘;’).

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

Parameters

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

A collation 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 function.They are passed by value into the procedure, so any changes inside the procedure has no effect on the parameters in the caller.Input parameters may have default values.Parameters with default values specified must be added 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.