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

Answer 1

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).