Snowflake procedure uncaught type error upd_stmnt.getSqlText is not a function in procedure

user_19184678 0
Viewed 59

Can someone please let me know what is wrong with this code? I am reading id, name, name1, name2 from a table and trying to update name1 and name2 in same table with name value.

create or replace procedure tbl_update()
returns varchar not null
language javascript as
$$
var select_sql     = {sqlText: `select id, name, name1, name2 from tbl;`};
var select_stmnt   = snowflake.createStatement(select_sql);
var select_result  = select_stmnt.execute();
var select_rec_cnt = select_result.getRowCount();
if(select_rec_cnt > 0)
  {
    while (select_result.next())
     {
       var v_id    = select_result.getColumnValue(1);
       var v_name  = select_result.getColumnValue(2);
       var v_name1 = select_result.getColumnValue(3);
       var v_name2 = select_result.getColumnValue(4);
       for (var i = 1; i <= 2; i++) {
          if(('v_name' + i).length == 0) {
              var upd_sql = `update tbl a set a.name` + i + `=` + `'` + v_name + `'`+
                            `where a.id=` + `'` + v_id + `'` + `;`;
              var upd_stmnt = snowflake.createStatement(upd_sql);
              upd_stmnt.execute();
            }
             else
              { return upd_stmnt.getSqlText(); }
        }
     }
else { return 'No records to process'; }
return 'done';
$$
snowflake-cloud-data-platform sql-update sql
1 Answers

Using stored procedure, result loop and nested for loop is completely uneccesary as all of this could be achieved with a single update. In general set-based approach is advised instead of singelton row-by-row processing.

UPDATE tbl
SET name1 = name
   ,name2 = name;

If two tables are involved then:

UPDATE tbl1
SET name1 = tbl2.name
   ,name2 = tbl2.name
FROM tbl2
WHERE tbl1.id = tbl2.id;

What I have not mentioned is that, name1 should get updated if it is null; name2 should get updated if name1 is not null and name2 is null in that order.. I have not coded for it. I am trying to accomplish simple update first.

UPDATE tbl
SET name1 = COALESCE(name1, name)
   ,name2 = COALESCE(name2, name1, name)
WHERE name1 IS NULL OR name2 IS NULL;
user_50708790
Related