While loop in snowflake

Question

I am working on a SQL script below on Snowflake worksheets and getting this error below. Tables are defined. Any feedbacks is appreciated.

Error:

Uncaught exception of type 'STATEMENT_ERROR' on line 9 at position 4 : SQL compilation error: error line 162 at position 34 invalid identifier 'CURRMONTH'

execute immediate $$ 
declare
    firstmonth :=(select dateadd(month,case when day(current_timestamp())<=10 then -1 else 0 end,dateadd(month,0,date_trunc('month',current_date()))));
    lastmonth  :=(select dateadd(month,1,dateadd(month,case when day(current_timestamp())<=10 then -1 else 0 end,dateadd(month,0,date_trunc('month',current_date())))));
    currmonth date default firstmonth;

begin        
 while (currmonth <=lastmonth) do 
    insert into temptable  
    select colA, colB, colC, colD
    from tableA 
    where date between currmonth and dateadd(day,-1,dateadd(month,1,currmonth))  and date2 = currmonth

 currmonth :=dateadd(month,1,currmonth)
end while;
end;
$$

Answer 1

try this, modified the column name from date to date1,

create or replace  table temptable  (date1 date,date2 date, colA varchar2, colB varchar2, colC varchar2, colD varchar2);
create or replace  table tableA  (date1 date,date2 date, colA varchar2, colB varchar2, colC varchar2, colD varchar2);


execute immediate $$ 
declare
    firstmonth :=(select dateadd(month,case when day(current_timestamp())<=10 then -1 else 0 end,dateadd(month,0,date_trunc('month',current_date()))));
    lastmonth  :=(select dateadd(month,1,dateadd(month,case when day(current_timestamp())<=10 then -1 else 0 end,dateadd(month,0,date_trunc('month',current_date())))));
    --currmonth date default firstmonth;
    currmonth date ;
begin
 currmonth :=firstmonth;
 while (currmonth <=lastmonth) do 
    insert into temptable (colA, colB, colC, colD)
    select colA, colB, colC, colD
    from tableA 
    where date1 between :currmonth and dateadd(day,-1,dateadd(month,1,:currmonth))  and date2 = :currmonth;

 currmonth :=dateadd(month,1,currmonth);
end while;
end;
$$
;