Execution error in store procedure SQL compilation error: invalid identifier 'TEST3' At Statement.execute

Question

I have created below stored Procedure but getting error while parsing VAL in where clause, i have tried double quotes " and backstick ` as well

create or replace TABLE T1 (
    COL VARCHAR(16777216)
);

insert into t1 values  ('test1');
insert into t1 values  ('test2');
insert into t1 values  ('test3');

create or replace procedure ifcopied(val varchar)
                                   returns varchar
                                   language javascript
                                   execute as caller
                                   as
                                   $$
                                   sql_command = "select * from t1 where col = " + VAL;
                                   var stmt = snowflake.createStatement({sqlText:sql_command});                       
                                   var res = stmt.execute();
                                   res.next();
                                   row_status = res.getColumnValue(1);
                                   return row_status                                   
                                   $$;

call ifcopied('test3')

;

getting below error

Execution error in store procedure IFCOPIED: SQL compilation error: error line 1 at position 29 invalid identifier 'TEST3' At Statement.execute, line 4 position 50

Execution error in store procedure IFCOPIED: SQL compilation error: error line 1 at position 29 invalid identifier 'TEST3' At Statement.execute, line 4 position 50

Answer 1

In addition to what Gokhan has you can also perform it this way.

sql_command = `select * from t1 where col = '${VAL}';`

This keeps down on your use of concatenation as well as allows you to see the variables in line.

Answer 2

You need to add single quotes or use bind variables:

create or replace procedure ifcopied(val varchar)
                                   returns varchar
                                   language javascript
                                   execute as caller
                                   as
                                   $$
                                   sql_command = "select * from t1 where col = '" + VAL + "'";
                                   var stmt = snowflake.createStatement({sqlText:sql_command});                       
                                   var res = stmt.execute();
                                   res.next();
                                   row_status = res.getColumnValue(1);
                                   return row_status                                   
                                   $$;

Recommend version with using bind variables:

create or replace procedure ifcopied(val varchar)
                                   returns varchar
                                   language javascript
                                   execute as caller
                                   as
                                   $$
                                   sql_command = "select * from t1 where col = ?";
                                   var stmt = snowflake.createStatement({sqlText:sql_command, binds:[ VAL ]});                       
                                   var res = stmt.execute();
                                   res.next();
                                   row_status = res.getColumnValue(1);
                                   return row_status                                   
                                   $$;