How to use the value from a result set within a for loop in Snowflake stored procedure?

Question

I am trying to run a data reconciliation stored procedure on each user that exists in the source table. To do this, I retrieve all distinct user IDs in that table and then run the procedure for each unique user. All of my tables and columns are in lower case so I need to use double quotes when passing the variable of table names.

CREATE OR REPLACE PROCEDURE refreshData(sourceTable VARCHAR, targetTable VARCHAR)
RETURNS BOOLEAN NOT NULL
LANGUAGE SQL
AS
    $$
        DECLARE
            res resultset DEFAULT (SELECT DISTINCT "user_id" AS USER_ID FROM IDENTIFIER(:sourceTable));
            cur CURSOR FOR res;
        BEGIN
        FOR record IN cur DO
            CALL userDataRefresh(record.user_id, sourceTable, targetTable);
        END FOR;
        RETURN TRUE;
        END;
    $$
;

-- example usage: CALL refreshData('"temp_table"', '"table"');

However, I get the error:

invalid identifier 'RECORD.USER_ID' (line 72)

As above, I've tried converting the column to upper case so as to not have to deal with it when calling userDataRefresh.

Any ideas?

Answer 1

Snowflake is a bit picky on where cursor variables can be used. The solution would be to assign the value of the cursor to a new variable.

You can use double quotes when referencing a case sensitive column in the cursor.

    CREATE OR REPLACE PROCEDURE refreshData(sourceTable VARCHAR, targetTable VARCHAR)
    RETURNS BOOLEAN NOT NULL
    LANGUAGE SQL
    AS
        $$
            DECLARE
            res resultset DEFAULT (SELECT DISTINCT "user_id" FROM IDENTIFIER(:sourceTable));
            cur CURSOR FOR res;
            user_id int;
        BEGIN
        FOR record IN cur DO
            user_id := record."user_id";
            CALL userDataRefresh(:user_id, sourceTable, targetTable);
        END FOR;
        RETURN TRUE;
        END;
        $$
    ;