How does suspend work in firebird?

Question

I have a table t with one column i of type int, with a few rows. I want to loop over it. Of course, I can write a select query for this. But I am learning procedures and wrote a procedure,

set term ^;
create procedure qt returns(a int) as
begin
    for select i from t into :a do
        suspend;
end^
set term ;^

But when I call this procedure, I only get one row back,

execute procedure qt;

I see,

           A 
============ 
           1 

I think I do not understand suspend.

Answer 1

Stored procedures with a SUSPEND in them are so called selectable procedures. You execute them using SELECT:

SELECT * FROM qt

or

SELECT * FROM qt()

The EXECUTE PROCEDURE statement is only for procedures that produce a single row result. If you use it for a selectable stored procedure, then it will only produce a single row (and exit when it hits SUSPEND).

In response to your inquiry about what is documented about this:

• The Interbase 6.0 Language Reference on page 177 says:

  SUSPEND should not be used in an executable procedure.

• On page 178 it shows a table of the behavior of SUSPEND, EXIT and END in selectable and executable procedures (slightly modified to fit):

Procedure type SUSPEND                EXIT                END
Selectable     • Suspends execution   Jumps to final END  • Returns control 
                 of procedure until                         to application
                 next FETCH is issued                     • Sets SQLCODE to 100 
               • Returns output                             (end of record stream)
                 values

Executable     • Jumps to final END   Jumps to final END  • Returns values
               • Not recommended                          • Returns control 
                                                            to application

• For a stored procedure (shown on page 178) that can produce multiple rows, page 179 describes the differences in behavior when executed with SELECT and executed with EXECUTE PROCEDURE.