Set custom value for system$set_return_value + snowflake

Question

I have been trying to set the return values of the task in snowflake.

I have a simple task that looks like this ..

create or replace procedure insertNum()
 returns boolean
 language javascript
 execute as caller
 as 
 $$
    var countResult = snowflake.createStatement({sqlText: "select count(*) from numbers;"}).execute();
    countResult.next();
    var count = countResult.getColumnValue(1);
    var result = snowflake.createStatement({sqlText: "insert into numbers(number) values(?)", binds: [count+1]}).execute();
    result.next();
    var stmt = snowflake.createStatement({sqlText:`call system$set_return_value(?);`, binds: [result.getColumnValue(1)]});
    stmt.execute();
    return result.getColumnValue(1);
 $$
 ;

I want to set the return value of the task which would appear as a return value in the snowflake task history SQL statement.

QUERY_ID  NAME  QUERY_TEXT  CONDITION_TEXT  STATE   ERROR_CODE  ERROR_MESSAGE RETURN_VALUE
01a2a31c-0c02-b429-0001-08de03e270c6    WRITETONUMBERS  call insertNum() FAILED 100183  Execution error in store procedure INSERTNUM:  SQL compilation error:  argument 1 to function SYSTEM$SET_RETURN_VALUE needs to be constant, found 'CAST(? AS VARCHAR)'  At Statement.execute, line 8 position 9     

But I'm getting errors as I'm supposed to do it

SQL compilation error:  argument 1 to function SYSTEM$SET_RETURN_VALUE needs to be constant, found 'CAST(? AS VARCHAR)'  At Statement.execute, line 8 position 9

Not able to find any concrete documentation around it. Any help here?

Answer 1

It looks like you need to pass in a constant/hardcoded value, rather than a bound variable.

Try this instead:

snowflake.createStatement({
  sqlText: `call system$set_return_value('${result.getColumnValue(1)}');`
});

Many parts of Snowflake are like this, like generators:

<count> and <sec> must be non-negative integer constants.

Although, in the case of system$set_return_value, it appears to be undocumented.