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
2

So many views, no answers. :frowning:

I have a similar requirement. I have a Snowflake stored procedure that inserts a record into a log table and returns the primary key from a sequence for that record as an integer. I need to keep that integer for use later when we want to mark the process as complete by inserting another log record with the same key.
I can return a value from a stored function. But the store function can’t insert a row.
The stored procedure call fails. :frowning:
macro:

{% macro test_get_single(process_name) %}

    {% set sql_statement %}
          SELECT dw_util.process_start_p('dbt', '{{ process_name }}', 'model', 'target: {{ target.name }}') AS start_instance_id
    {% endset %}

    {{ log("Executing SQL: " ~ sql_statement, info=True) }}

    {%- set start_instance_id = dbt_utils.get_single_value(sql_statement, default="-1") -%}

    {{ log("start_instance_id: " ~ start_instance_id, info=True) }}
  
    {# Return the process_instance_id to use it later #}
    {{ return(start_instance_id) }}

{% endmacro %}

 dbt run-operation test_get_single --args '{"process_name": "TEST"}'      ✘ 1 
19:29:41  Running with dbt=1.8.6
19:29:41  Registered adapter: snowflake=1.8.3
19:29:42  Found 378 models, 1 analysis, 2 seeds, 574 data tests, 210 sources, 579 macros
19:29:42  Executing SQL: 
          SELECT dw_util.process_start_p('dbt', 'TEST', 'model', 'target: dev') AS start_instance_id
19:29:43  Encountered an error while running operation: Database Error
  002141 (42601): SQL compilation error:
  Unknown user-defined function DW_UTIL.PROCESS_START_P

I’ve tried it with a call stored procedure syntax like this:

{% macro test_get_single(process_name) %}

    {% set sql_statement %}
          call dw_util.process_start_p('dbt', '{{ process_name }}', 'model', 'target: {{ target.name }}') 
    {% endset %}

    {{ log("Executing SQL: " ~ sql_statement, info=True) }}

    {%- set start_instance_id = dbt_utils.get_single_value(sql_statement, default="-1") -%}

    {{ log("start_instance_id: " ~ start_instance_id, info=True) }}
  
    {# Return the process_instance_id to use it later #}
    {{ return(start_instance_id) }}

{% endmacro %}

Then I get this result, even though I can execute the line in snowflake:

dbt run-operation test_get_single --args '{"process_name": "TEST"}'                                                                                                                                                                                                           ✘ 1 
19:33:43  Running with dbt=1.8.6
19:33:43  Registered adapter: snowflake=1.8.3
19:33:45  Found 378 models, 1 analysis, 2 seeds, 574 data tests, 210 sources, 579 macros
19:33:45  Executing SQL: 
          call dw_util.process_start_p('dbt', 'TEST', 'model', 'target: dev') 
    
19:33:46  Encountered an error while running operation: Database Error
  000904 (42000): Uncaught exception of type 'STATEMENT_ERROR' on line 61 at position 8 : SQL compilation error: error line 6 at position 29
  invalid identifier 'SQLCODE'

Any suggestions would be much appreciated.

I’ve also tried versions where I just call the stored procedure without using the dbt_util.get_single_value macro. Similar failure when running the stored procedure.