How to return result from a stored procedure to TASK RETURN_VALUE in snowflake?

Question

I would like to return logging and status messages from a stored procedure to the TASK that calls it.

create or replace procedure status_return()
  returns string not null
  language javascript
  as
  $$
    var result_status = 'The return status and debug information in string format';
  return result_status; // Statement returned for info/debug purposes
  $$;

I would like to pass the result from stored procedure call status_return() back to the task

-- Create a task that calls the stored procedure every hour
create or replace  task call_SP
  warehouse = SMALL
  schedule = '1 minute'
as
call status_return();

When I execute TASK_HISTORY to view RETURN_VALUE is always empty.

select * 
from table(information_schema.task_history(SCHEDULED_TIME_RANGE_START => dateadd(hours, -5, current_timestamp()) , 
                               TASK_NAME => 'call_sp'));

How can I view the result of a stored procedure in task_history for SUCCESS, FAILURE, or ERRORS?

I have tried creating a task in the following way, but I was unsuccessful and it return with errors.

create or replace  task call_SP
  warehouse = EDS_SMALL
  schedule = '1 minute'
as
call system$set_return_value(call status_return());

Can I use Javascript in Tasks? To store the result of a stored procedure call into a variable and return it back to the TASK result

Answer 1

In order to be able to get a RETURN_VALUE in your TASK_HISTORY you have to set the return_value in your stored procedure using call system$set_return_value(). Examples can be found in snowflake documentation.

This is what it should looks like if you want the return_value field of the task_history to return your result status var when your task is launched :

create or replace procedure status_return()
  returns string not null
  language javascript
  as
  $$
  var result_status = 'The return status and debug information in string format';
  var rv_stmt = snowflake.createStatement({sqlText:`call system$set_return_value('` + result_status  + `');`});
  var rv_res = rv_stmt .execute(); // Set return_value

  return result_status; // Statement returned for info/debug purposes
  $$;