CODEWITHSUNDEEP
functionsnowflake-cloud-data-platform
Siddharth Dattani
Siddharth Dattani

Reputation: 21

Passing multiple values to a parameter in Snowflake table function

Is it possible to pass multiple values to a parameter in Snowflake Table function? For example, if we have country parameter - can we pass multiple values to the country and then use the parameter in the query using IN clause?

Upvotes: 2

Views: 2583

Answers (2)

Simon D
Simon D

Reputation: 6279

There are multiple ways that you could achieve this, here is the one that came to mind:


create or replace function get_ids(FirstNames Array)
returns table (id number)
as $$
 select "CustomerKey"::Number 
 from dim_customer
 where lower("FirstName") in (
    select value from table(flatten(input=>FirstNames))
  )
$$
;

select * 
from table(get_ids(array_construct('jon', 'ruben', 'simon', 'shannon')));

Upvotes: 1

Hans Henrik Eriksen
Hans Henrik Eriksen

Reputation: 2870

You can use an ARRAY to package multiple parameters into one, eg

SELECT ARRAY_CONSTRUCT('FR', 'IN', 'UK', 'NO') My_Multiparam_Array;

If you don't have scalar values, but eg. a table column, you have to construct the ARRAY with ARRAY_AGG().

Then use ARRAY_CONTAINS() instead of IN:

SELECT ARRAY_CONTAINS('FR'::VARIANT, My_Multiparam_Array); -- True if 'FR' is in the array

Upvotes: 3

Related Questions