sqlstored-proceduressnowflake-cloud-data-platform

Create FOR loop inside Snowflake stored procedure to execute SQL query list from table


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);

Solution

  • 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:

    enter image description here