How to suspend work in firebird?

Question

I have a table t with one column i have type int, with several rows. I want to fixate on him. Of course, I can write a choice for this. But I study the procedures and wrote the 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 get only one line,

 execute procedure qt; 

I see,

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

I think I do not understand suspend .

Answer 1

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

 SELECT * FROM qt 

or

 SELECT * FROM qt() 

The EXECUTE PROCEDURE intended only for procedures that produce the result of a single line. If you use it for a selectable stored procedure, then it will produce only one line (and exit when it gets to SUSPEND ).

In response to your request for what is documented:

• Interbase 6.0 Language Reference on page 177 says:

  SUSPEND should not be used in an executable procedure.

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

 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 (see page 178) that can create multiple lines, page 179 describes the differences in behavior when executed with SELECT and executes with EXECUTE PROCEDURE .