Storing return value of a stored procedure in DBT Macro

Help
1

Hi all,

I have below macro which calls a Snowflake Stored procedure. This procedure returns a number value, we need to get that value in dbt macro and pass to a model calling this macro.
So, basically we need to perform below steps -

  1. Call Stored proc in dbt macro
  2. Fetch return value of Stored proc in dbt macro
  3. Run this macro in dbt model.

Stored Proc Definition
CREATE OR REPLACE PROCEDURE test_proc(i_job_id NUMBER(38,0),i_step_id number(38,0),i_database_name char(100))
RETURNS VARCHAR(2000)
LANGUAGE SQL
EXECUTE AS CALLER
AS ’
declare
RES VARCHAR;
BEGIN
Insert into table1 values (:i_job_id, :i_step_id, :i_database_name);
RES := ‘‘SUCCESSFUL’’;
RETURN RES;
END;
';

Macro Code
{% set call_stmt = “Call test_proc(param1, param 2, param3, param4);” %}
{% set result = run_query(call_stmt) %}
{% do return(result) %}

Expected return value - WHile calling stored proc directly in DB returning the expected value
Successful

Actual Return Value in result variable

column data_type
GENERIC_JOB_AUDIT_CREATE_SP Text