5

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!

jarlh
  • 42,561
  • 8
  • 45
  • 63
Sujata Shakya
  • 51
  • 1
  • 2

2 Answers2

8

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'));
Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
0

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!

Jeremy DiBattista
  • 61
  • 3