Snowflake loop through array to run stored procedure

Question

I've created an array which houses my stored procedure parameters. How do I loop through the array, and input the values into my stored procedure as parameters?

Here is what I have so far:

CREATE OR REPLACE PROCEDURE SP_TL_START()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
//Array created to house parameters. 
var report_users = [];

// create for the following users
var rs = snowflake.execute( { sqlText: 
`
SELECT 
DISTINCT USERS

    FROM USERS_TABLES
`} );

//load user values from table into Array,  we will be looping through the array to execute the store proc

while (rs.next()){
    var report_user_id = rs.getColumnValue(1);
    report_users.push(report_user_id);
                  }
//run store proc for each user - format for store proc = SP_TL_RUN(USERVALUE,DATE);                  

for (var i = 0; i < report_users.length; i++) {
        snowflake.execute( { sqlText: 'CALL SP_TL_RUN(report_users[i], TO_VARCHAR(SUBSTRING(DATEADD(DAY,-1,'2021-07-09'),1,10))) ;'});
       
        }
$$;
    
                                              
CALL SP_TL_START ()

I'm getting the following error:

JavaScript compilation error:
Uncaught SyntaxError: Unexpected number in SP_TL_START at ' snowflake.execute( { sqlText: 'CALL SP_TL_RUN(report_users[i], TO_VARCHAR(SUBSTRING(DATEADD(DAY,-1,'2021-07-09'),1,10))) ;'});' position 114

I tried to loop through the array (report_users) and print the values, but Snowflake would not allow me to console.log(report_users[i]), and kept resulting in null when I called it.

I know for a fact my array has the following values

Gokhan Atil · Accepted Answer · 2021-07-12 07:21:10Z

You should use double quotes for string instead of single quote, and send the JS variable as a bind variable:

CREATE or replace PROCEDURE SP_TL_START()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
//Array created to house parameters. 
var report_users = [];

// create for the following users
var rs = snowflake.execute( { sqlText: 
`
SELECT 
DISTINCT USERS

    FROM USERS_TABLES
`} );

//load user values from table into Array,  we will be looping through the array to execute the store proc

while (rs.next()){
    var report_user_id = rs.getColumnValue(1);
    report_users.push(report_user_id);
                  }
//run store proc for each user - format for store proc = SP_TL_RUN(USERVALUE,DATE);                  

for (var i = 0; i < report_users.length; i++) {
        snowflake.execute( { sqlText: "CALL SP_TL_RUN(?, TO_VARCHAR(SUBSTRING(DATEADD(DAY,-1,'2021-07-09'),1,10))) ;", binds: [report_users[i]] });
       
        }
$$;

Collectives™ on Stack Overflow

Snowflake loop through array to run stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related