Snowflake pass dynamic value binding and constant value

Question

I have to pass the dynamic value as my column name to my table from the loop and the constant value . My code snippet something looks like below: While(res.next()){ var columnname= res.getColumnValue(1); var stmt= insert into table1(column1,column2,column3) select column1,'Too Long',+columnname+from table2; \var result= \snowflake.createStatement(stmt); \executing the statement

Getting error as invalid identifier if I pass the constant value. How can we achieve this?

Answer 1

It seems there's a syntax error on your procedure. Be careful about how you escaped single quote character. It's hard to debug it without seeing the actual source but here's a sample script to demonstrate there is nothing special with the constant value:

CREATE or REPLACE PROCEDURE TEST( VAR1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS '
var columnname = VAR1

for (var i = 0; i< 5; i++){
    var stmt = snowflake.createStatement( { sqlText: "insert into table1(column1,column2,column3) select column1,''Too Long''," + columnname + " from table2" } ); 
    res = stmt.execute();
    }   
return ''YES''
';

To test the above procedure, I create two tables and insert a row to table2:

create table table1(column1 varchar,column2 varchar,column3 varchar);

create table table2(column1 varchar,columnX varchar);

insert into table2 values ('testing','secretcol');

I call the procedure with "columnX" variable, I did not want write more complex procedure to read the column name from 3rd table:

call test( 'columnX' );

When I query the table1, I see 5 records. Each row contains: 'testing','Too long' and 'secretcol' values.

select * from table1;