雪花通过数组循环运行存储过程

lzfw57am  于 2021-09-13  发布在  Java
关注(0)|答案(1)|浏览(281)

我已经创建了一个数组,其中包含我的存储过程参数。如何循环数组,并将值作为参数输入存储过程?
以下是我到目前为止的情况:

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

我得到以下错误: 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 我试图循环遍历数组(report_users)并打印值,但snowflake不允许我这样做 console.log(report_users[i]) ,并在调用它时一直导致null。
我知道我的数组有以下值

mefy6pfw

mefy6pfw1#

您应该对字符串使用双引号,而不是单引号,并将js变量作为绑定变量发送:

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

        }
$$;

相关问题