Javascript Array in snowflake procedure

Question

I have a snowflake procedure that creates a table using the parameters passed to a snowflake procedure as below:

CREATE OR REPLACE PROCEDURE SP_test (c_names varchar)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS $$
  
  var results_array = [];
  var c_names_combined = "'" + C_NAMES.split(',').join("','") + "'"
  
  sql_command = `SELECT TABLE_CATALOG||'.'||TABLE_SCHEMA||'.'||TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='MAP_MEMBER_PLATFORM'`
  sql_command = sql_command + ` AND TABLE_SCHEMA IN (` + c_names_combined +`)`
  sqlStmt = snowflake.createStatement( {sqlText: sql_command} );
  result_sqlStmt  = sqlStmt.execute();
  
  while (result_sqlStmt.next()) {
    var full_table_names = result_sqlStmt.getColumnValue(1);
    results_array.push([full_table_names]);
  }
  var c_1 = results_array[0];
  var c_2 = results_array[1];
  var c_3 = results_array[2];

  
  sql_command = `CREATE OR REPLACE TABLE table_to_create
                          AS
                          SELECT * FROM ` + c_1 +`
                           UNION ALL
                          SELECT * FROM ` + c_2 +`
                           UNION ALL
                          SELECT * FROM ` + c_3 +``
  sqlStmt = snowflake.createStatement( {sqlText: sql_command} );
  result_sqlStmt  = sqlStmt.execute();
  
  result = "done"
  return result
  
$$
; 
call SP_test('A,B,C');

The code is working fine if i pass three parameters A, B and C. But i want it to work for as many parameters i pass.

For example if i call the procedure with parameters ('A, B, C, D, E'). I want my table(table_to_create) create a table using the parameters i have passed.(in this case: A, B, C, D, E)

Please help !

@Sergiu : Thanks, but i am already doing what is answered there. I am splitting the parameters and already using it my 1st Select statement. What i want is to use the result of that 1st Select statement in my Create table statement. Column value may be of more than 3 rows (which i have mentioned above). I want to create a table based on the number of row values i get from getColumnValue(1). Hope you understand what i am trying to explain. — Puspa Bista
– Puspa Bista, Commented Dec 3, 2021 at 13:18
I believe i need to use a loop in my Create statement to use array values. Not exactly sure how to do it. — Puspa Bista
– Puspa Bista, Commented Dec 3, 2021 at 13:25

Sergiu · Accepted Answer · 2021-12-03 13:36:16Z

1

Something like this should work, might not be ideal, but should work:

var results_array = [1, 2, 3, 4, 5];
var sql_command="CREATE OR REPLACE TABLE table_to_create AS ";
for (let i = 0; i < results_array.length; i++) {
   sql_command += "SELECT * FROM " + results_array[i] + " UNION ALL ";
 }

var last_words = sql_command.split(" ")
last_words.pop();
last_words.pop();
last_words.pop();
var sql_command_new = last_words.join(" ");

Running this on node console I get:

> console.log(sql_command_new);
CREATE OR REPLACE TABLE table_to_create AS SELECT * FROM 1 UNION ALL SELECT * FROM 2 UNION ALL SELECT * FROM 3 UNION ALL SELECT * FROM 4 UNION ALL SELECT * FROM 5

The 3 pop's are needed to remove last space, and last UNION ALL. In this way you can add as many args as you need.

answered Dec 3, 2021 at 13:36

Sergiu

4,7761 gold badge16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Puspa Bista Over a year ago

Many Thanks @Sergiu . This works perfectly fine. Exactly what is was looking for.

Collectives™ on Stack Overflow

Javascript Array in snowflake procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related