# macro return value

**URL:** <https://discourse.getdbt.com/t/macro-return-value/8133>\
**Category:** Help\
**Tags:** macros\
**Created:** [May 3, 2023, 10:35pm UTC](https://discourse.getdbt.com/t/macro-return-value/8133 "2023-05-03T22:35:08Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![ck82](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@ck82](https://discourse.getdbt.com/u/ck82)\
**Post date:** [May 3, 2023, 10:35pm UTC](https://discourse.getdbt.com/t/macro-return-value/8133/1 "2023-05-03T22:35:08Z")

</div>

I have below macro

```auto
{% macro get_department_name(value) %}

    {% set test_sql %}
        select deptid, description from {{ ref('raw_department') }} where deptid = '{{value}}'
    {% endset %}
 
    {% set results = run_query(test_sql) %}

    {% if execute %}
        {% for row in results.rows %}
            {%- set retval = row[1] -%}
        {% endfor %}
    {% endif %}
    {{return(retval)}}
{% endmacro %}           

```

I am calling the above macro from a select statement

```auto
select e.deptid,
{{macro_hello_world_5('e.deptid')}}
from {{ ref('raw_employee') }} e

```

I am getting below error  
“Database Error in rpc request (from remote system.sql) 001003 (42000): SQL compilation error: syntax error line 3 at position 0 unexpected ‘from’.”

compiled code looks like below:

```auto
select e.deptid,
from ANALYTICS.dbt_vsnowflake.raw_employee e

```

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [May 4, 2023, 12:00am UTC](https://discourse.getdbt.com/t/macro-return-value/8133/2 "2023-05-04T00:00:54Z")

</div>

I think replacing `{{return(retval)}}` with `{{retval}}` will mean a value is templated into your query, but I’m not convinced that will do what you want. Why are you using Jinja vs just joining in SQL?

Note: `@Josh Devlin (he)` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1683158428858829?thread_ts=1683153331.987029&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [May 4, 2023, 4:14am UTC](https://discourse.getdbt.com/t/macro-return-value/8133/3 "2023-05-04T04:14:49Z")

</div>

I agree with Josh - this should be done in standard SQL. This macro’s query will not execute for every variant of the `e.deptid` - Jinja macros are evaluated and resolved as a static string during the compilation stage.

Instead your code should be:

```auto
select 
  e.deptid,
  d.description
from {{ ref('raw_employee') }} e
inner join {{ ref('raw_department') }} d 
  on e.deptid = d.deptid

```

---

<div class="post-metadata">

**Author:** ![Surya](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/surya/32/3763_2.png) [@Surya](https://discourse.getdbt.com/u/Surya)\
**Post date:** [May 4, 2023, 5:39am UTC](https://discourse.getdbt.com/t/macro-return-value/8133/4 "2023-05-04T05:39:57Z")

</div>

> [@ck82](#):
>
> `{%- set retval = row[1] -%}`

@ck82 looks like ur accessing the variable `retval` outside of the `if block` but it was initialized in side`for` loop. The scope of the variable `retval` is limited to `for loop`

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [May 4, 2023, 6:06am UTC](https://discourse.getdbt.com/t/macro-return-value/8133/5 "2023-05-04T06:06:07Z")

</div>

> [@Surya](#):
>
> looks like ur accessing the variable `retval` outside of the `if block` but it was initialized in side`for` loop.

This is true, but to be clear: fixing that won’t make the macro behave the way you want

---

<div class="post-metadata">

**Author:** ![Surya](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/surya/32/3763_2.png) [@Surya](https://discourse.getdbt.com/u/Surya)\
**Post date:** [May 4, 2023, 7:20am UTC](https://discourse.getdbt.com/t/macro-return-value/8133/6 "2023-05-04T07:20:09Z")

</div>

Got it, Thanks @joellabes

---

<div class="post-metadata">

**Author:** ![ck82](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@ck82](https://discourse.getdbt.com/u/ck82)\
**Post date:** [May 4, 2023, 11:56am UTC](https://discourse.getdbt.com/t/macro-return-value/8133/7 "2023-05-04T11:56:01Z")

</div>

Hi Joellabes,  
Agree, this is one of the solutions. I was experimenting on macros and want to see the “return” behavior in macros and experimented this way.  
we generally call a function in oracle/snowflake and implement the same logic like I did in this macro to decrease the number of joins in the calling sql, so tried the similar way here.  
So, the way I am trying to use the macro to return a value is wrong and dbt wouldnt support this?

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [May 9, 2023, 4:24am UTC](https://discourse.getdbt.com/t/macro-return-value/8133/8 "2023-05-09T04:24:36Z")

</div>

Macros aren’t the same as UDFs - they are rendered once and return a static string of SQL.

If you want to create a UDF in dbt, check out [Using dbt to manage user defined functions](https://discourse.getdbt.com/t/using-dbt-to-manage-user-defined-functions/18)