databasesnowflake-schemasnowflake-tasksnowflake-pipesnowflake-cloud-data-platform

Snowflake pass dynamic value binding and constant value


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?


Solution

  • 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;