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

I think this is developer mistake - call procedure with SELECT and
don't put SUSPEND in it

> 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 use simple rule : if procedure has output parameters - i call it with
SELECT * FROM , if there are no outputs - call with EXECUTE. I think
connectivity drivers can follow this too

> I see the need to unifying the way stored procedures are handled.
>
> 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.

This is kind of syntax sugar, imho, i don't like it

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

SELECT can be done only from those procedures which has outputs.
Personally i don't see a big problem with current dual syntax, but if we
decide
to add SUSPEND to procedures with outputs and without SUSPEND, i'll don't
resist ;)

Regards,
Vlad