Can we pass list or array as parameter to JavaScript procedure in snowflake?

Question

Can we pass list or array as parameter to javascript procedure in snowflake?

I am working on procedure which would run weekly and delete a week old data from certain tables. Instead of creating task for individual table, I want to pass table names as list/array.

Please guide.

Thanks in advance!

Rosinski

Answer 1

Yes, you can pass an array into a Snowflake JavaScript stored procedure. Here's a sample:

create or replace procedure test(ARR array)
returns string
language javascript
as
$$
    var i; 
    var out = "";

    // Remember to capitalize variables input to the stored procedure definition
    for(i = 0; i < ARR.length; i++){       
        out += ARR[i];
        if (i < ARR.length - 1) out += ", ";
    }
    return out;
$$;


call test(array_construct('TABLE_1', 'TABLE_2', 'TABLE_3'));

Match

Thanks.. modified my proc with this for loop..it helped.. :) – Rosinski

Answer 2

I struggled with this for a while, and have come up with a good 1 liner to make an array digestible by a procedure.

myArr = "array_construct('" + MyArr.join("', '") + "')"

this can then be inserted into any call statement like such

var sql_command = `call my_proc(${myArr});`

Hope this helps anyone else who stumbles upon this!

Abixah