Subject RFC: Please unify stored procedure execution
Author Roman Rokytskyy
Problem definition:

Firebird distinguishes two types of procedures: normal procedures and
selectable ones. Former should be called using EXECUTE PROCEDURE
statement, latter with SELECT * FROM ... statement.

If EXECUTE PROCEDURE is used to call selectable procedure, only first
row of the result set is returned. If SELECT * FROM ... is applied to
a "normal" (i.e. non-selectable procedure), an empty result set is

There is no flag in RDB$PROCEDURES system table telling whether
procedure is selectable or not. Connectivity dirvers that are required
to translate abstract procedure call syntax (for example {call ...}
syntax in JDBC) cannot automatically generate appropriate SQL.

I see the need to unifying the way stored procedures are handled.


Multiple solutions are possible:

a) provide a new SQL statement, for example CALL, that when applied to
selectable procedures, returns complete result set (is equivalent to
SELECT * FROM), when applied to non-selectable procedures returns a
result set with single row corresponding to the RETURNS declaration.

b) Fix the PSQL compiler to add SUSPEND before exiting the
non-selectable procedure if SUSPEND is not already contained in the
procedure body (in other words, automatically convert non-selectable
procedure into selectable). This allows using SELECT * FROM syntax for
all types of procedures.