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 !
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.