How to loop through a SNOWFLAKE cursor and declare a string (cell value with delimiter) to a variable and pass inside where clause IN statement

I am trying to run a stored procedure which loops through a cursor gets the cell values into variables which I then pass it inside the where clause to filter. One of the cell contains a string with multiple values delimited by comma. I need to get these values into a variable and pass that variable into a where clause IN statement.

I have 3 tables

1. Filter Criteria Table. I get these individual cell values into variables.

2. MAIN DATA TABLE. this is the table I filter against.

EXPECTED Output Result

3.OUTPUT TABLE.

My code Below doesn't seem to work.

I am not sure how to do this using arrays which I believe it the right approach, any help will be appreciated. Thanks

 DECLARE 
    v_country STRING;
    v_item_list STRING;
    v_order_qty NUMBER;
    c1 CURSOR for (select * from FILTER_CRITERIA_TABLE);
    res RESULTSET;
    res_out RESULTSET;
BEGIN
    FOR rec IN c1 DO
            v_country := rec.COUNTRY ;
            v_item_list := rec.ITEM_LIST ;
            v_order_qty := rec.ORDER_QTY ;              
            res:=(
                    INSERT INTO OUTPUT_TABLE
                    
                    SELECT 
                        COUNTRY,
                        ORDER_NUMBER,
                        ITEM,
                        QTY,
                    FROM MAIN_DATA_TABLE
                    WHERE   
                        ITEM IN ( :v_item_list )
                        AND
                        COUNTRY = :v_country
                    );  
    END FOR;    
    res_out:=(select* from OUTPUT_TABLE);   
    RETURN TABLE(res_out); 
END;

Since you mentioned you wanted to use array variable, I have just made slight modification to your code.

• C1 cursor selects only items and country as we need those to filter the main table.

SELECT country,TRIM(VALUE::STRING) AS item
FROM filter_criteria_table,
LATERAL FLATTEN(input => SPLIT(ITEM_LIST, ','))

• In each iteration of the loop, INSERT runs and a row is inserted.

Approach using variables

CREATE OR REPLACE PROCEDURE filter_data()
RETURNS TABLE ()
LANGUAGE SQL
AS
$$
DECLARE 
    v_country STRING;
    v_item_list STRING;
    c1 CURSOR for (SELECT
            country,TRIM(VALUE::STRING) AS item
        FROM filter_criteria_table,
        LATERAL FLATTEN(input => SPLIT(ITEM_LIST, ',')));

    res_out RESULTSET;
BEGIN
    FOR rec IN c1 DO
        v_country := rec.COUNTRY;
        v_item_list := rec.item;
        
                   
            INSERT INTO OUTPUT_TABLE
            SELECT 
                COUNTRY,
                ORDER_NUMBER,
                ITEM,
                QTY
            FROM MAIN_DATA_TABLE
            WHERE   
                 upper(ITEM) = upper(:v_item_list)
                AND COUNTRY = :v_country
       ;
    END FOR;

    res_out := (SELECT * FROM OUTPUT_TABLE); 
    RETURN TABLE(res_out); 
END;
$$;

Calling the procedure

CALL filter_data();

Outputs