1

I want to use the row_variable from a for loop as a bind variable,

FOR <row_variable> IN <cursor_name> DO
    statement;
    [ statement; ... ]
END FOR [ <label> ] ;

DECLARE
    total_price FLOAT;
    c1 CURSOR FOR SELECT * FROM invoices;
    query VARCHAR DEFAULT 
'SELECT * FROM invoices WHERE price > ? AND price < ?';
BEGIN
    total_price := 0.0;
    OPEN c1;
    FOR rec IN c1 DO
        total_price := total_price + rec.price;
        EXECUTE IMMEDIATE :query USING (rec.price1, rec.price2))
    END FOR;
    CLOSE c1;
    RETURN total_price;
END;

The example above is made up only to highlight how I´d like to use the query rec is each of the rows of the resultset and rec.column would be a reference to one of those values,in this case rec.price1 and rec.price2, but when I try to use it as a bind variable an error shows up (probably due to the ´´.´´ (dot) ).

I found a work around, which is assigning that ´´rec.column´´ value to a variable first and then using that as the bind variable, but that would mean creating a variable for each of the columns to use, which is not really ideal.

How can I reference these values directly as a bind variable?

user18243519
  • 23
  • 2
  • Possible duplicate of [Invalid Identifier Problem in my stored procedure](https://stackoverflow.com/a/75644083/5070879). There is an open idea describing exactly this case. [Snowflake Ideas](https://community.snowflake.com/s/ideas) - Search for: **"Direct for-cursor loop variable reference with SQL statements"** – Lukasz Szozda May 26 '23 at 13:42

0 Answers0