Snowflake - How to add to RESULTSET in a loop

Question

PROBLEM - I'm only getting the result set for last loop execution

QUESTION: How can I concatenate/add to resultset?

DETAILS

What I'm trying to do:

1. take patientID and Date from table 1 using CURSOR

c_row CURSOR FOR SELECT PatientID, PIDATE FROM table_1

2. Sample of table_1

table_1

3. build and run dynamic query on table 2 based on the values of the cursor. Add the results to RESULTSET for returning at completion of loop.

for r_row in c_row do
        v_SQL := 'SELECT * FROM table_2 where patientid =' || r_row.PatientID || ' AND TO_DATE(time) < ''' || r_row.PIDATE ||'\'';
        rs_out := (EXECUTE IMMEDIATE :v_SQL);
END FOR;
RETURN TABLE(rs_out);
CLOSE c_row;

EXPECTED RESULTS: Results of queries generated using each lines of table_1 are populated into the result set (rs_out)

ACTUAL RESULTS: Only the results from last execution is returned

I'm pretty sure it's because rs_out is keep getting overwritten with query results rather than concatenating.

rs_out := rs_out || (EXECUTE IMMEDIATE :v_SQL); caused error :)

Instead of using results set I might INSERT into a table but not sure how that'll work as the dynamic query from step 2 is likely to return multiple rows..

Any help will assist with my job security :) thanks for your time

Entire code

DECLARE 
    v_SQL text;
    c_row CURSOR FOR SELECT PatientID, PIDATE FROM table_1;
    rs_out RESULTSET;
BEGIN
    for r_row in c_row do
        v_SQL := 'SELECT * FROM table_2 where patientid =' || r_row.PatientID || ' AND TO_DATE(time) < ''' || r_row.PIDATE ||'\'';
        rs_out := (EXECUTE IMMEDIATE :v_SQL);
    END FOR;
    RETURN TABLE(rs_out);
    CLOSE c_row;
END;

Answer 1

We can use temp table to hold the intermediate result and return that.

Also as a good practice, we should close the cursor before the return statement else that line will never execute.

Find the sample code below:

DECLARE 
    v_SQL text;
    c_row CURSOR FOR SELECT PatientID, PIDATE FROM table_1;
    rs_out RESULTSET;
BEGIN
    create or replace temporary table temp_result like table_2;
    
    for r_row in c_row do
        
        v_SQL := 'insert into temp_result SELECT * FROM table_2 where patientid =' || r_row.PatientID || ' AND TO_DATE(time) <= ''' || r_row.PIDATE ||'\'';
        EXECUTE IMMEDIATE :v_SQL;
        
    END FOR;
    CLOSE c_row;
    rs_out := (select* from temp_result);
    RETURN TABLE(rs_out);
    
END;