Below is the stored procedure which uses for loop using java script
create or replace procedure test()
returns varchar
language javascript
execute as caller
as
$$
for (var i = 1; i<= 10; i++)
{
var command8=` update table a set a.c1='some' from table b where a.source='gg' and b.c2=a.c2 and b.c3=a.c3 and (datediff(year,date(a.etl_create),current_date())= '"+i+"')`
var stmt8=snowflake.createStatement({sqlText: command8});
var rs8=stmt8.execute();
}
Using bidning variables:
for (var i = 1; i<= 10; i++)
{
var command8=` update table a set a.c1='some'
from table b
where a.source='gg' and b.c2=a.c2 and b.c3=a.c3
and (datediff(year,date(a.etl_create),current_date())= :1)`
var stmt8=snowflake.createStatement({sqlText: command8, binds:[i]});
var rs8=stmt8.execute();
}
To substitute a variable into a template literal, use ${variable}.
var command8=` update table a set a.c1='some' from table b where a.source='gg' and b.c2=a.c2 and b.c3=a.c3 and (datediff(year,date(a.etl_create),current_date())= '${i}')`