CODEWITHSUNDEEP
javascriptsnowflake-cloud-data-platformprocedure
Puspa Bista
Puspa Bista

Reputation: 85

Javascript Array in snowflake procedure

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 !

Upvotes: 3

Views: 856

Answers (1)

Sergiu
Sergiu

Reputation: 4578

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.

Upvotes: 1

Related Questions