Looping Stored Procedure

Mugi Yanto wrote:

Anyone could give me sample rows / script of Stored Procedure for looping (SELECT QUERY) ?

Helen Borrie answers:

create procedure xxxx (....)
returns (.....)
as
declare var1 integer;
declare var2 whatever,
....;
begin

FOR
   SELECT COL_1, COL_2, ...
   FROM ATABLE
   WHERE...
   .....
INTO :var1, :var2, ......
DO
   BEGIN
      [operate on the current set of variable values]
      SUSPEND; (only if you are sending a row back to the client)
   END
....
end

Of course, there is much more to it than that, more than you could expect from a single support posting. PSQL language is an area of study in its own right. Part Seven of The Firebird Book comprises 120 pages and five chapters on this subject and it already needs at least one more! You can embed a FOR...SELECT loop inside another one as well, by a variety of means.

The FOR SELECT....INTO...DO syntax is one way. There are other ways. For example, study the PSQL section of the release notes for Firebird 2.x, under the topic 'Explicit Cursors'.