Javascript Procedure to create tables in Snowflake

Question

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?

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2021-04-02T18:35:23

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.

Javascript Procedure to create tables in Snowflake

1 Answers