cursor for loop & dynamic SQL – Snowflake

August 18, 2022

I’m attempting to write a procedure that takes in a list of tables and date_column to create some row_counts by calendar dates for reconciliation purposes.

SELECT t.*
FROM (
  VALUES ('tbl1', 'created_date')
       , ('tbl2', 'modify_date')
       , ('tbl3', 'last_seen_date')
  ) t(tbl, dt)

+----+--------------+
|TBL |DT            |
+----+--------------+
|tbl1|created_date  |
|tbl2|modify_date   |
|tbl3|last_seen_date|
+----+--------------+

I’m connected to Snowflake via a JDBC connection using Datagrip – so I assume I need to follow the classic SnowSQL part of the documentation:

https://docs.snowflake.com/en/developer-guide/snowflake-scripting/loops.html#cursor-based-for-loops

EXECUTE IMMEDIATE $$

  DECLARE
    dt text
    , tbl text;
    c1 CURSOR FOR SELECT dt, tbl from t;
    BEGIN
      FOR record in c1 DO
        dt := record.dt
        tbl := record.tbl
        stmt =: 'SELECT COUNT(*)' ||
           CONCAT(', DAYOFMONTH(', $dt, ')') ||
           CONCAT('\n FROM ', $tbl) ||
           CONCAT('\n WHERE YEAR(', $dt, ')', ' = YEAR(CURRENT_DATE)') ||
           CONCAT('\n AND MONTH(', $dt, ')', ' = MONTH(CURRENT_DATE)') ||
           '\n GROUP BY' ||
           CONCAT('\n DAYOFMONTH(', $dt, ')')
      EXECUTE IMMEDIATE stmt -- will adapt this to be an update statement eventually. 
      END FOR
      end;
$$

This returns a SQL Compilation error, I’ve tried a few different variations of this but I’m none the wiser on how to proceed.

>Solution :

There are lots of minor issues like missing semicolons etc. Here is the fixed script:

DECLARE
    dt text;
    tbl text;
    stmt text;
    c1 CURSOR FOR SELECT dt, tbl from t;
BEGIN
  FOR record in c1 DO
    dt := record.dt;
    tbl := record.tbl;
    stmt := 'SELECT COUNT(*)' ||
       CONCAT(', DAYOFMONTH(', dt, ')') ||
       CONCAT('\n FROM ', tbl) ||
       CONCAT('\n WHERE YEAR(', dt, ')', ' = YEAR(CURRENT_DATE)') ||
       CONCAT('\n AND MONTH(', dt, ')', ' = MONTH(CURRENT_DATE)') ||
       '\n GROUP BY' ||
       CONCAT('\n DAYOFMONTH(', dt, ')');
   -- EXECUTE IMMEDIATE :stmt; 
     RETURN stmt;
  END FOR;
END;