EXECUTE BLOCK

Description: 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.

Because this block has input parameters, it has to be prepared first. Then the parameters can be set and the block executed.
It depends on the client software how this must be done and even if it is possible at all – see the notes below.

Our last example takes two integer values, smallest and largest. For all the numbers in the range smallest .. largest, the block outputs the number itself, its square, its cube and its fourth power.

Again, it depends on the client software if and how you can set the parameter values.

Notes:

Some clients, especially those allowing the user to submit several statements at once, may require you to surround the EXECUTE BLOCK statement with SET TERM lines, like this:

set term #;
execute block (...)
as
begin
statement1;
statement2;
end
#
set term ;#

In Firebird's isql client you must set the terminator to something other than “;” before you type in the EXECUTE BLOCK statement. Otherwise isql, being line-oriented, will try to execute the part you have entered as soon as it encounters the first semicolon.

Executing a block without input parameters should be possible with every Firebird client that allows the user to enter his
or her own DSQL statements. If there are input parameters, things get trickier: these parameters must get their values after
the statement is prepared but before it is executed. This requires special provisions, which not every client application
offers. (Firebird's own isql, for one, doesn't.)

The server only accepts question marks (“?”) as placeholders for the input values, not “:a”, “:MyParam” etc., or literal values. Client software may support the “:xxx” form though, which it will preprocess before sending it to the server.

If the block has output parameters, you must use SUSPEND or nothing will be returned.

Output is always returned in the form of a result set, just as with a SELECT statement. You can't use RETURNING_VALUES or execute the block INTO some variables, even if there's only one result row.

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.

Domains instead of datatypes

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.