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.

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;

• sql
• stored-procedures
• snowflake-cloud-data-platform

• "I have 3 tables" - but you only show 2? – Dale K Commented Mar 11 at 7:24
• The third Table is the output table where I will be inserting the result set into. Thanks – Aaqil Rahman Commented Mar 11 at 18:13
• In that you have 2 tables and you wish to create a third. – Dale K Commented Mar 11 at 18:25

2 Answers 2

You do not need a procedure for this. The following query should return the expected result:

with country_items as 
(
    select f.country, v.value::STRING item  from FILTER_CRITERIA_TABLE f,
    lateral flatten( split( item_list, ',') ) v
)
select m.* from MAIN_DATA_TABLE m
join country_items c on m.country = c.country and upper(m.item) = upper(c.item);

If you need to insert the values:

insert into OUTPUT_TABLE
with country_items as 
(
    select f.country, v.value::STRING item  from FILTER_CRITERIA_TABLE f,
    lateral flatten( split( item_list, ',') ) v
)
select m.* from MAIN_DATA_TABLE m
join country_items c on m.country = c.country and upper(m.item) = upper(c.item);

If you want a SP:

CREATE OR REPLACE PROCEDURE my_sp()
RETURNS TABLE ()
LANGUAGE SQL
AS
$$
DECLARE
    res_out RESULTSET;
BEGIN
    insert into OUTPUT_TABLE
    with country_items as 
    (
       select f.country, v.value::STRING item  from FILTER_CRITERIA_TABLE f,
       lateral flatten( split( item_list, ',') ) v
    )
    select m.* from MAIN_DATA_TABLE m
    join country_items c on m.country = c.country and upper(m.item) =    upper(c.item);
    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