Insert Query not inserting records via javascript procedure in snowflake

Question

I'm new to javascript so please ignore bad programming convention.

I am trying to insert some records in tableA(say) and using insert into (select statement) methodology. This insertion is supposed to happen when an if condition returns TRUE (a==null)

Procedure is getting created successfully. But When I call the procedure even though the code is going into the if branch (checked via return text) the actual insertion is not happening.

(already tried commit as advised in a different requester's post)

Summary:

OUTPUT: code reached here so..success.

ISSUE: When I check the tableA after calling SP proc(), there is no new record inserted.

I have already checked all the SQL query outside of the SP proc and they are working fine. All the table and variable names are changed for privacy reasons.

Please find below my procedure code and expected and actual outcomes and help me understand what am I doing wrong.

create or replace procedure proc()
  returns string
  language javascript
  as 
  $$

 var a = ('select max(fw) from  tableA where fy in (select max(fy)from tableB b join tableC c on\ c.id=b.uuid);').execute;

 if (a == null)
 {
 ('insert into tableA\
  (select\
   c.fy,\
   c.fw,\
   sum(b.somenumber) Total,\
   count(distinct b.uuid) Count,\
   (Total/Count) AOV\
   from tableB b\
   join tableC c\
   on c.id=b.uuid\
   where fy in (select max(fy)from tableB b join tableC c on c.id=b.uuid)\
   group by FY,FW\
   order by FY,FW)').execute;
return 'code reached here so..success'
}
else {
  return 'nothing'
     }
  $$;

call proc();

EXPECTED: 'code reached here so..success' and Records should be inserted in tableA (as they are being inserted when I test this SQL outside of Stored Procedure proc.).

ACTUAL: only 'code reached here so..success' gets returned and NO RECORD INSERTED

Answer 1

Please check how you can run a SQL statement inside of a JS stored procedure:

https://docs.snowflake.com/en/sql-reference/stored-procedures-javascript.html#basic-examples

Something like this:

create or replace procedure proc()
returns string
language javascript
as 
$$
 var myResultSet = snowflake.execute( {sqlText:  'select max(fw) from  tableA where fy in (select max(fy)from tableB b join tableC c on c.id=b.uuid);' } ); 


     if (myResultSet.next())
     {
       snowflake.execute({sqlText: `insert into tableA
      (select
       c.fy,
       c.fw,
       sum(b.somenumber) Total,
       count(distinct b.uuid) Count,
       (Total/Count) AOV
       from tableB b
       join tableC c
       on c.id=b.uuid
       where fy in (select max(fy) from tableB b join tableC c on c.id=b.uuid)
       group by FY,FW
       order by FY,FW)` } );
       return 'code reached here so..success';
    }
    else {
      return 'nothing';
         }
$$;