CREATE PROCEDURE

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