I'm trying to create a stored proc in snowflake which checks if the 1st of the month is a bank holiday or a weekend, and updates it to the next working day. it seems to be getting stuck in a loop. I have the bank holidays stored in a table so I want to check if the date matches any values in that table and if it does, to take the next working day.
create or replace procedure check_dt(run_dt DATE)
returns string
language javascript
execute as caller
as
$$
function batch_date(ts){
var a = new Date(ts);
var dt = new Date(a.setDate(1));
var flag = false;
while(flag==false){
var sql_stment = `select 1 from calender where bh_date = date'` + dt.toLocaleDateString() + `'`;
var result = snowflake.execute({sqlText: sql_stment});
while(result.next()){
if(result.getColumnValue(1) == 1){
dt.setDate(dt.getDate() + 1);
}
else if (dt.getDay() == 0){
dt.setDate(dt.getDate() + 1);
}
else if(dt.getDay() == 6){
dt.setDate(dt.getDate() + 2);
}
else{
flag = true;
break;
}
}
}
var batch_dt = batch_date(RUN_DT);
return batch_dt;
$$;
I've realised by using the result.next() if the date doesn't exist in the table it never reaches the clause to update the flag, so ends in an infinite loop. the below has fixed my problem. By using row count instead, if no values are present it will continue through the if-else statement
create or replace procedure check_dt(run_dt DATE)
returns string
language javascript
execute as caller
as
$$
function batch_date(ts){
var a = new Date(ts);
var dt = new Date(a.setDate(1));
var flag = false;
while(flag==false){
var sql_stment = `select 1 from calender where bh_date = date'` + dt.toLocaleDateString() + `'`;
var result = snowflake.execute({sqlText: sql_stment});
if(result.getRowCount() > 0){
dt.setDate(dt.getDate() + 1);
}
else if (dt.getDay() == 0){
dt.setDate(dt.getDate() + 1);
}
else if(dt.getDay() == 6){
dt.setDate(dt.getDate() + 2);
}
else{
flag = true;
}
}
var batch_dt = batch_date(RUN_DT);
return batch_dt;
$$;