“Executable” Stored Procedure Examples Of

CommentsProcedural SQL (PSQL) Statements

“Executable” Stored Procedure

The EXECUTE PROCEDURE statement is most commonly used to invoke “executable” stored procedures to perform some data-modifying task at the server side — those that do not contain any SUSPEND statements in their code.They can be designed to return a result set, consisting of only one row, which is usually passed, via a set of RETURNING_VALUES() variables, to another stored procedure that calls it.Client interfaces usually have an API wrapper that can retrieve the output values into a single-row buffer when calling EXECUTE PROCEDURE in DSQL.

Invoking “selectable” stored procedures is also possible with EXECUTE PROCEDURE, but it returns only the first row of an output set which is almost surely designed to be multi-row.Selectable stored procedures are designed to be invoked by a SELECT statement, producing output that behaves like a virtual table.

Note

In PSQL and DSQL, input parameters may be any expression that resolves to the expected type.
Although parentheses are not required after the name of the stored procedure to enclose the input parameters, their use is recommended for the sake of readability.
Where output parameters have been defined in a procedure, the RETURNING_VALUES clause can be used in PSQL to retrieve them into a list of previously declared variables that conforms in sequence, data type and number with the defined output parameters.
The list of RETURNING_VALUES may be optionally enclosed in parentheses and their use is recommended.
When DSQL applications call EXECUTE PROCEDURE using the Firebird API or some form of wrapper for it, a buffer is prepared to receive the output row and the RETURNING_VALUES clause is not used.

Examples of `EXECUTE PROCEDURE`

In PSQL, with optional colons and without optional parentheses:

EXECUTE PROCEDURE MakeFullName
  :FirstName, :MiddleName, :LastName
  RETURNING_VALUES :FullName;

In Firebird’s command-line utility isql, with literal parameters and optional parentheses:
```
EXECUTE PROCEDURE MakeFullName ('J', 'Edgar', 'Hoover');
```
Note

In DSQL (e.g. in isql), RETURNING_VALUES is not used.Any output values are captured by the application and displayed automatically.

A PSQL example with expression parameters and optional parentheses:

EXECUTE PROCEDURE MakeFullName
  ('Mr./Mrs. ' || FirstName, MiddleName, upper(LastName))
  RETURNING_VALUES (FullName);

`EXECUTE BLOCK`

Creates an “anonymous” block of PSQL code in DSQL for immediate execution

Available in

DSQL

Syntax

EXECUTE BLOCK [(<inparams>)]
  [RETURNS (<outparams>)]
  <psql-module-body>

<inparams> ::=  <param_decl> = ? [, <inparams> ]

<outparams> ::=  <param_decl> [, <outparams>]

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

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

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

Table 1. Arguments for the `EXECUTE BLOCK` Statement Parameters
Argument	Description
param_decl	Name and description of an input or output parameter
paramname	The name of an input or output parameter of the procedural block, up to 63 characters long.The name must be unique among input and output parameters and local variables in the block
collation	Collation

Executes a block of PSQL code as if it were a stored procedure, optionally with input and output parameters and variable declarations.This allows the user to perform “on-the-fly” PSQL within a DSQL context.

“Executable” Stored Procedure Examples Of