I am trying to create a loop inside a stored procedure in snowflake. I have a table created which has 3 columns (groupqueryids, query, orderid). One groupqueryids can have multiples queries and shorted by orderid. User will input the groupqueryids to execute the query set they want to execute and final query output will store the table in csv format.
I have created the query which is not working. Need help.
CREATE OR REPLACE PROCEDURE myprocedure()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
-- Snowflake Scripting code
DECLARE
count1 INTEGER DEFAULT 0;
c1 CURSOR FOR SELECT query FROM qset WHERE groupquery='' ORDER BY orderid;
res1 resultset default(SELECT query FROM qset);
text1 VARCHAR;
BEGIN
FOR record IN c1 DO
text1 := record.query;
LET res RESULTSET := (EXECUTE IMMEDIATE :text1);
-- res1 := res;
END FOR;
RETURN text1;
END;
$$
;
CALL myprocedure(GQID02);
This procedure works for me:
create or replace table t1 (groupquery varchar(10), query string, orderid int);
insert into t1 values ('GDID01', 'select * from citibike_trips limit 1', 1);
insert into t1 values ('GDID02', 'select * from citibike_trips limit 10', 2);
SELECT query FROM t1 WHERE groupquery='GDID02' ORDER BY orderid;
CREATE OR REPLACE PROCEDURE myprocedure(gq varchar)
RETURNS table()
LANGUAGE SQL
AS
$$
-- Snowflake Scripting code
DECLARE
count1 INTEGER DEFAULT 0;
res resultset DEFAULT (SELECT query FROM t1 WHERE groupquery=:gq order by orderid);
c1 CURSOR FOR res;
text1 VARCHAR;
rs resultset;
BEGIN
FOR record IN c1 DO
text1 := record.query;
rs := (EXECUTE IMMEDIATE :text1);
END FOR;
RETURN table(rs);
END;
$$
;
CALL myprocedure('GDID02');
I get back: