Snowflake dynamic procedure statement.Execute() return value

Question

I have a snowflake procedure using javascript. the procedure exec the statement and return a single value result into a var: var res = stmt.execute(); I want insert this value into a table. but this value is return as an object. How do I work around the solution?

My script is dynamic and has a while loop that generates multiple statements, but here is a simplified version to focus on what I need to do get Thx

create Procedure GetLastUpdateDate() returns boolean language javascript as $$

sql_command = "select max(Updatedate) from MyTable;";

var stmt = snowflake.createStatement({ sqlText: sql_command});
var res = stmt.execute();
 
    snowflake.createStatement( { sqlText: 'INSERT INTO MYTABLE (NCOLUMN) VALUES (?)',   
                                    binds: [res]
                                } ).execute();    
                     
$$
;

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2021-08-10T18:00:14

.getColumnValue() could be used:

getColumnValue(colIdx|colName)

This method returns the value of a column in the current row (i.e. the row most recently retrieved by next()).

sql_command = "select max(Updatedate) from MyTable;";

var stmt = snowflake.createStatement({ sqlText: sql_command});
var res = stmt.execute();

while (res.next()) {
 var myVal = res.getColumnValue(1);

 snowflake.createStatement( { sqlText: 'INSERT INTO MYTABLE (NCOLUMN) VALUES (?)',   
                             binds: [myVal]
                             } ).execute();   
}

If more than one row is expected loop should be used: while (res.next()) {...}

Snowflake dynamic procedure statement.Execute() return value

1 Answers