how to get the output of a stored procedure as task output in snowflake

Question

I created a stored procedure where it returns the string with a successful message and the number of rows inserted or error messages like file not found or data did not load when executed manually. when I called the same stored procedure with task it shows(task_history) as succeed. and cant find if the data has been loaded or not. it has to be checked manually.

when I referred the following question Snowflake a working procedure is not being successfully executed when calling it within a scheduled task

the procedure and the task has the same owner(owner has global execute task privilege).

but data is being updated both the times during manual and task call of procedure.

how to make the return value appear in task and make the task not executing the successor task if the stored procedure return a error.

Answer 1

You can use SYSTEM$SET_RETURN_VALUE to set a return value in you first task. SYSTEM$SET_RETURN_VALUE Documentation

In a tree of tasks, a task can call this function to set a return value. Another task that identifies this task as the predecessor task (using the AFTER keyword in the task definition) can retrieve the return value set by the predecessor task.

You can then use SYSTEM$GET_PREDECESSOR_RETURN_VALUE in your next task to condition your actions (for example doing nothing if the return_value contains an error).

The return value will appear for monitoring in TASK_HISTORY. TASK_HISTORY Documentation

Answer 2

There are 2 parts to get the return value from stored procedure to save to task history.

1. The stored procedure should "EXECUTE AS CALLER"
2. You need to call system$set_return_value with the return value

Example

CREATE OR REPLACE PROCEDURE MySchema.MyStoredProcedure()
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$

    let command1 =  = `DELETE FROM MySchema.MyTable WHERE rownum > 10;`;
    let stmt1 = snowflake.createStatement({sqlText: command1});
    let rs = stmt1.execute();
    rs.next();
    let deleted = rs.getColumnValue(1);

    //This requires the SP to be run as CALLER - This return value is logged in Task History
    stmt1 = snowflake.createStatement({sqlText:`call system$set_return_value(' { "RowsDeleted": ${deleted} }');`});
    rs = stmt1.execute();

    return { "RowsDeleted": deleted };

$$;