Snowflake while loop

Question

I am working on migrating SQL Server stored procedure and loading the data to Snowflake using DBT. I am having some issues with using insert within while loop. Would really appreciate if anyone has feedbacks on this.

Error:

SQL Compilation error: syntax line 7 position 18 unexpected '('.
Syntax error line 8 at position 12 unexpected 'insert'.

Temp tables are defined already.

execute immediate $$ 
begin 
 set firstmonth = '2022-03-01'; 
 set lastmonth = '2022-04-01'; 
 set currmonth = $firstmonth;
 
 while ($currmonth <$lastmonth) do   
   insert into tmptable( col1, col2)  
   select 1,2 from tableA;
   currmonth = dateadd(month, 1,$currmonth);
 end while;
end;
$$
;

Answer 1

The syntax needed is slightly different, fixed:

execute immediate $$ 
declare
 firstmonth date default '2022-03-01';
 lastmonth date default '2022-04-01';
 currmonth date default firstmonth;

begin 
 while (currmonth < lastmonth) do   
   insert into tmptable( col1, col2)  
   select 1,2 from tableA;
   currmonth := dateadd(month, 1, currmonth);
 end while;
return currmonth;
end;
$$