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();
    }

CodePudding user response：

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}')`

CodePudding user response：

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();
}