# Storing return value of a stored procedure in DBT Macro

**URL:** <https://discourse.getdbt.com/t/storing-return-value-of-a-stored-procedure-in-dbt-macro/12343>\
**Category:** Help\
**Created:** [March 6, 2024, 12:19pm UTC](https://discourse.getdbt.com/t/storing-return-value-of-a-stored-procedure-in-dbt-macro/12343 "2024-03-06T12:19:38Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![pragya.yadav](https://avatars.discourse-cdn.com/v4/letter/p/9d8465/32.png) [@pragya.yadav](https://discourse.getdbt.com/u/pragya.yadav)\
**Post date:** [March 6, 2024, 12:19pm UTC](https://discourse.getdbt.com/t/storing-return-value-of-a-stored-procedure-in-dbt-macro/12343/1 "2024-03-06T12:19:38Z")

</div>

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 |

---

<div class="post-metadata">

**Author:** ![bpruss](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/bpruss/32/4290_2.png) [@bpruss](https://discourse.getdbt.com/u/bpruss)\
**Post date:** [November 12, 2024, 7:23pm UTC](https://discourse.getdbt.com/t/storing-return-value-of-a-stored-procedure-in-dbt-macro/12343/2 "2024-11-12T19:23:19Z")

</div>

So many views, no answers. ☹

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. ☹  
macro:

```auto
{% 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 %}

```

```auto
 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:

```auto
{% 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:

```auto
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.