Hello Roman,

What can I say ... "I feel your pain", would probably do.

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

There's no flag, because the "being selectable or not" isn't absolute.

Sometimes, you want a procedure that's both selectable and executable.
Having a unified solution would be best, I guess.

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

Or simply let EXEC return a resultset (if possible).

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

This is a "no go", IMO. Why not make it so that if there's no SUSPEND,
simply return the output param values to the caller, despite of what it's
called like?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com