How to pass column name as parameter in macros which is calling a procedure in snowflake

I am having a macro which is executing a procedure in snowflake and fetching a value. I am facing a issue to pass column value as parameter in Macros in a model
macro:
{% macro cal_prc_macro(param1,param2,param_date) %}
{% set query1 -%}
{% set var1 = param1 | as_number %}
{% set var2 = param2 | as_number %}
{% set var_date = param_date %}
call prac ({{ var1 }} , {{ var2 }} , to_date({{ var_date }},‘dd/mm/yyyy’)) ;
{%- endset %}
{% set results = run_query(query1) %}
{% if execute %}
{% set results_list = results.columns[0].values() %}
{% else %}
{% set results_list = %}
{% endif %}
{{ return (results_list[0]) }}
{% endmacro %}

model:
with tab as (
select col1, col2,
to_varchar(cal_date::date,‘dd/mm/yyyy’) as date_val
from {{ ref(‘table_name’) }}
)
select {{ cal_prc_macro(‘a.cal1’ , ‘a.col2’, ‘date_val’ ) }} as LOA
from tab a

Server error: Database Error in rpc request (from remote system)
000904 (42000): SQL compilation error: error line 1 at position 43
invalid identifier ‘COL1’

Is there any work around for this?

I’m having trouble understanding what you’re trying to do, but if what you want is to get a comma-separated list of column names of a dbt model, try the macro “star” from dbt-utils.

If not, please give us more details on what exactly you’re trying to achieve…

Hi , Thanks for coming back.
My objective is, I have created a procedure in Snowflake data warehouse in which I need to pass 3 parameters and the procedure is going to return a value which I needed for my data model.
To accomplish the task I have created a macro which will call the procedure and it will pass the procedure return value to the module.

step1: my procedure in snowflake is prac ( p_v VARIANT, p_n VARIANT, p_calc DATE)
step2: now from the macro cal_prc_macro I am trying to pass parameter’s values to the procedure and execute it.
step3. trying to call the macro in module by passing the column values as below,

select {{ cal_prc_macro(‘a.cal1’ , ‘a.col2’, ‘date_val’ ) }} as LOA
from tab a

do you have idea can it be possible to pass column name in macros and pass the same to database procedure and get the return value at the same time for each records.

OK, if the objective is to insert the value of a stored procedure into the compiled SQL, I’d use run_query, which returns a Python Agate table with the results of a query:

{% set macro_query %}
  call prac ({{ var1 }} , {{ var2 }} , to_date({{ var_date }},‘dd/mm/yyyy’)) ;
{% endset %}
{% set results = run_query(macro_query) %}
select '{{ results.columns[0].values()[0] }}'

Let me know whether I understood the problem correctly this time

Hi,
I was trying the approach you mention in my macro but I am getting the below error, did you use this code any place.
I am given the code as below,

{% macro cal_prc_loa(param1,param2,param_date) %}
{% set query1 -%}
{% set var1 = param1 | as_number %}
{% set var2 = param2 | as_number %}
{% set var_date = param_date %}
call prac ({{ var1 }} , {{ var2 }} , to_date({{ var_date }},‘dd/mm/yyyy’)) ;
{%- endset %}
{% set results = run_query(query1) %}
select ‘{{ results.columns[0].values()[0] }}’
{% endmacro %}

in module:
select col1, {{ cal_prc_loa(col1,col2, to_date(col3,‘dd/mm/yyyy’)) }}
from table

Error:

Server error: Compilation Error in rpc request (from remote system)
‘None’ has no attribute ‘table’

in macro cal_prc_loa (macros/cal_prc_loa.sql)
called by rpc request (from remote system)

Oh yeah, my bad, a call to run_query will only return something when execute mode is on. I haven’t tried it but this should be closer to working code.

{% if execute %}
  {% set macro_query %}
    call prac ({{ var1 }} , {{ var2 }} , to_date({{ var_date }},‘dd/mm/yyyy’)) ;
  {% endset %}
  {% set results = run_query(macro_query) %}
  select '{{ results.columns[0].values()[0] }}'
{% else %}
  select 'dummy value'
{% endif %}
1 Like