CODEWITHSUNDEEP
sqlstored-proceduresfirebird
user3505049
user3505049

Reputation: 149

Executing two FOR SELECT statement in SP firebird

Is it possible to have 2 FOR SELECT statement in stored procedure where the 1st statement will display all of its data then the 2nd statement will continue when the 1st statement is done .

I already tried this one:

for select id,date,particular
from TABLE_NAME
  into :id, :date, :particular
do begin
  suspend;
end

for select id,date,payor
from TABLE_NAME
  into :id, :date, :payor
do begin
   suspend;
end

the first statement performs well but the second statement displays some data of the column particular of the 1st statement.

Upvotes: 1

Views: 622

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108941

This is possible, but there are two things you need to take into account:

  1. A selectable stored procedure only has one result set, so for the client it is not possible to detect this without some kind of discriminator column.
  2. If you don't reassign a value, the last assigned value is returned for each subsequent row.

This second item is the problem you are having, to solve this, you need to clear the value of particular, eg by adding the line particular = null; between the first and select for select .. block:

for select id,date,particular
from TABLE_NAME
  into :id, :date, :particular
do begin
  suspend;
end

particular = null;

for select id,date,payor
from TABLE_NAME
  into :id, :date, :payor
do begin
   suspend;
end

However if the code in your question is the actual code, you might want to consider using a view instead with two selects combined with union all. This is likely to have less overhead.

Upvotes: 1

Related Questions