I have tried below code to create tables that listed in tables array, but i get error:
JavaScript compilation error: Uncaught SyntaxError: Unexpected end of input in CREATE_TABLES_SP at '
CREATE OR REPLACE PROCEDURE create_tables_sp() //Procedure to create tables
RETURNS string
LANGUAGE JAVASCRIPT
AS
$$
var tables = ['table_a', 'table_b', 'table_c'];
for (var table of tables){
try {
snowflake.execute (
{sqlText: "create or replace table PROD_DB.schema1.${table} (ename varchar);"}
);
return "Succeeded."; // Return a success/error indicator.
}
catch (err) {
return "Failed: " + err; // Return a success/error indicator.
}
$$;
Does anyone know what i'm missing?
Using IDENTIFIER to provide table name as "variable' and binds
to set actual value:
CREATE OR REPLACE PROCEDURE create_table_sp()
RETURNS string
LANGUAGE JAVASCRIPT
AS
$$
var tables = ['table_a', 'table_b', 'table_c'];
for (var table of tables) {
try {
snowflake.execute(
{
sqlText: `CREATE OR REPLACE TABLE IDENTIFIER(?)(ename VARCHAR);`
,binds: ['PROD_DB.schema1.' + table]
}
);
}
catch(err){
return "Failed: " + err;
};
};
return "Succeded.";
$$;
Check:
CALL create_table_sp();
SELECT * FROM PROD_DB.schema1.table_a;
SELECT * FROM PROD_DB.schema1.table_b;
SELECT * FROM PROD_DB.schema1.table_c;
The proposed usage of ${table}
(string interpolation) would also work:
CREATE OR REPLACE PROCEDURE create_table_sp()
RETURNS string
LANGUAGE JAVASCRIPT
AS
$$
var tables = ['table_a', 'table_b', 'table_c'];
for (var table of tables) {
try {
var sql = `CREATE OR REPLACE TABLE PROD_DB.schema1.${table} (ename VARCHAR);`;
snowflake.execute(
{ sqlText: sql }
);
}
catch(err){
return "Failed: " + err;
};
};
return "Succeded.";
$$;
But it is less secure and prone to SQL Injection attacks.