A continuación se muestra el procedimiento almacenado que utiliza for loop usando 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(); }Uso de variables de oferta :
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(); }Para sustituir una variable en un literal de plantilla, 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}')`