How to check if an SQL ResultSet is null and then use it in an IF condition of javascript stored-procedure in Snowflake?

Question

In a javascript stored procedure, basically I want to store the output of an SQL in a variable.

**This output could either be a four digit number OR null. **

If it is null I want to do "Action A"

If it is some four digit number I want to do "Action B"

Please help how to do it as both of the below are not working to achieve my goal:

1.) if (var a == null) {do actionA} else {do actionB}

2.) if (var a.next()) { do actionA} else {do actionB}

3.) please suggest a way to do it. It's possible that it could be due to data type of my variable that method 1 and 2 are not working.

create or replace procedure proctest()
returns string
language javascript
as 
$$
 var a = snowflake.execute( {sqlText:'select max(fw) from  tableA where fy = 2021;' } ); 


 if (a == null) // or if(a.next())
 {
   snowflake.execute({sqlText: `insert into tableX
  (select
   *
   from tableY
   where <some condition>`} );
   return 'inserted for null';
}
else {
 snowflake.execute({sqlText: `insert into tableX
  (select
   *
   from tableZ
   where <some condition>
   and <some condition>`} );
   return 'inserted for not null';
     }
 $$;

The above is not working and it never recongnizes correctly when a is null even though it is. It could be due to datatype string while a is storing result of sql query but I don't know if exactly that is the problem and which data type to use to get the desired result, if it is.

Answer 1

You need to get the value from the resultset using getColumnValue method. Here is a sample script to test various scenarios:

create table table1 (id number ) as select 1;

create or replace procedure proc()
returns string
language javascript
as 
$$
 var myResultSet = snowflake.execute( {sqlText:  'select null from table1' } ); // test case: returns NULL
// var myResultSet = snowflake.execute( {sqlText:  'select 1 from table1' } ); // test case: returns non-NULL
// var myResultSet = snowflake.execute( {sqlText:  'select null from table1 where 1<>1' } );  // test case: no rows

     if (myResultSet.next()) // it returned a value
     {
       var value = myResultSet.getColumnValue(1); // get the value of first column
       
       if (value) { return 'non null value returned' }
          else 
          { return 'null value returned'};
    }
    else {
      return 'no rows returned';
   }
$$;

call proc();