Accessing each row in a table and calling a procedure multiple times

Question

GET_STRING_LIST takes a string list as input and returns an ordered table of different IDs in a variable :RES_TABLE. I would like to take every row (ID) in :RES_TABLE and call a procedure GET_VALUES for 2 different coefficients. The way the code is now, it only returns data for one ID but I need it to return data for every ID.

BEGIN

    NUMBER1 = 0;
    NUMBER2 = 0;
    COUNTER = 0;
    
    FOR
      SELECT ID
        from GET_STRING_LIST(:VAR_ID)
        order by ID
        INTO :RES_TABLE
      DO BEGIN
        SELECT COUNT(ID) FROM GET_STRING_LIST(:VAR_ID) INTO COUNTER;    
        WHILE (COUNTER > 0) DO BEGIN
            select RES_VALUE
            from GET_VALUES(:RES_TABLE, '%', 'SOME_STRING1', 7)
            into :NUMBER1;  
            SELECT RES_VALUE
            FROM GET_VALUES(:RES_TABLE, '%', 'SOME_STRING2', 7)
            INTO :NUMBER2;
            COUNTER = COUNTER -1;
        END

  END
--  SUSPEND;
END

Please provide a [mre], including example data and expected results for the example data. As it stands the code shown in your question is not valid Firebird code, because it misses either `EXECUTE BLOCK` or `CREATE PROCEDURE` and the necessary parameter or variable definitions. However, if I had to guess, the problem is that you need to add `SUSPEND;` **within** the `WHILE` loop (so it emits a row for each iteration), instead of no `SUSPEND` or only a `SUSPEND` at the end of the procedure (which causes it to emit only one row, with the last values) — Mark Rotteveel, Jun 10 '22 at 12:25
As an aside, I have a hard time following the logic of your code, and I wonder if this wouldn't be better solved with joins between those stored procedures. To me it seems like you are repeatedly executing the same queries with the exact same parameters, which would be very inefficient. — Mark Rotteveel, Jun 10 '22 at 12:29
Suspend did the trick and I could remove the while loop to obtain the desired result. Thank you. — nemi, Jun 10 '22 at 13:05

Mark Rotteveel · Answer 1 · 2022-06-10T14:18:47.143

You need to add a SUSPEND in your loop. Without a SUSPEND, a stored procedure is an executable procedure which emits a single row once the procedures ends.

With SUSPEND statements, a stored procedure becomes a so-called selectable stored procedure. In the code in your question, you have a commented-out SUSPEND statement at the very end of your procedure. Although removing the comment marker would make your stored procedure selectable, having the SUSPEND at the very end of your stored procedure would make it suspend only one row (with the final values).

When Firebird encounters a SUSPEND statement, it will emit a row with the current values and wait for it to be fetched. So, you need to place the SUSPEND at the position in the code where your row is complete, and before you want to move on to calculating/retrieving values for the next row. By a cursory glance of your code, it should probably be within the WHILE loop (or maybe the FOR SELECT loop, but that would indicate that the WHILE is unnecessary).

Accessing each row in a table and calling a procedure multiple times

1 Answers1