I am trying to run a SQL query inside a Javascript Snowflake Stored Procedure but it isn't working and it keeps failing to initialise

Question

I am trying to create a Javascript Stored Procedure in Snowflake where the javascript executes a sql query. I am new to javascript and I stole all of it from an existing one that already exists (and works). The problem is that the stored procedure never initializes when I call it, and when I cancel the query, it returns an error:

SQL compilation error: error line 4 at position 28
invalid identifier 'TABLE_NAME'
At Statement.execute, line 20 position 23 (line 37)

My Stored Procedure is this:

 CREATE OR REPLACE procedure TRIAL
    (
    DATABASE_NAME VARCHAR,
    SCHEMA_NAME VARCHAR,
    TABLE_NME VARCHAR,
    COLUMN_NME VARCHAR
    )
    RETURNS varchar(100)
    language javascript
    as '   

    var return_value = "";

    var sql_command= `INSERT INTO XXX.YYY.END_TABLE (table_name, column_name, issue_code,parameters,value,row_hash, exception_ts)
                    WITH CTE AS (
                            SELECT 
                            ${TABLE_NME},
                            ${COLUMN_NME},
                            ''DQ_IS_NULL'',
                            ''NULLs not permitted'',
                            HASH(*) AS ROW_HASH,
                            CURRENT_TIMESTAMP()
                            FROM ${DATABASE_NAME}.${SCHEMA_NAME}.${TABLE_NME}
                            )
                    SELECT CTE.* FROM CTE
                    LEFT JOIN XXX.YYY.END_TABLE dql
                    ON CTE.ROW_HASH = dql.ROW_HASH WHERE dql.ROW_HASH IS NULL;`
    var stmt1 = snowflake.createStatement({sqlText: sql_command});
    var result_scan=stmt1.execute();
 
       return "Records Inserted";        
    ';

CALL TRIAL('DATABASE_NAME','SCHEMA_NAME','TABLE_NME','COLUMN_NME');

When calling the stored procedure, I have purposefully left in the variable names as an example.

Is anybody able to help?

Answer 1

You need to surround the TABLE_NME value with single quotes in the SELECT projection:

var sql_command= `INSERT INTO XXX.YYY.END_TABLE (table_name, column_name, issue_code,parameters,value,row_hash, exception_ts)
                WITH CTE AS (
                        SELECT 
                        ''${TABLE_NME}'',
                        ${COLUMN_NME},
                        ''DQ_IS_NULL'',
                        ''NULLs not permitted'',
                        HASH(*) AS ROW_HASH,
                        CURRENT_TIMESTAMP()
                        FROM ${DATABASE_NAME}.${SCHEMA_NAME}.${TABLE_NME}
                        )
                       ...