Snowflake passing parameters

user_6027446 0
Viewed 62

I have a function with parameters. If I pass null as the parameter in a where clause it brings back all results eg

RETURNS TABLE (CUSTOMER_CODE VARCHAR, CUSTOMERNAME VARCHAR)
LANGUAGE SQL
AS '                                  
  SELECT CUSTOMER_CODE, CUSTOMERNAME FROM DIM_CUSTOMER
   WHERE CUSTCODE IS NULL OR CUSTOMER_CODE = CUSTCODE
'

I would like to change the SQL to if the parameter CUSTCODE = 'ALL' then pass NULL value and bring back all results else CUSTOMER_CODE = CUSTCODE

Thank you in advance!

Leon

snowflake-cloud-data-platform parameter-passing
2 Answers

Use a CASE statement.

Example -

create or replace function test_fun (CUSTOMER_CODE number, CUSTOMERNAME VARCHAR)
RETURNS TABLE (CUSTOMER_CODE number, CUSTOMERNAME VARCHAR)
AS 'SELECT C_CUSTKEY, C_NAME FROM customer WHERE C_CUSTKEY= 
(case when customer_code=1 then NULL else CUSTOMER_CODE end)';
user_139153130

Try the below method:

create or replace table customer (
  CUSTOMER_CODE int, 
  CUSTOMERNAME varchar
);
insert into customer values (1, 'customer 1'), (2, 'customer 2');

create or replace function test_fun (CODE varchar)
RETURNS TABLE (CUSTOMER_CODE number, CUSTOMERNAME VARCHAR)
AS
$$
SELECT 
    CUSTOMER_CODE, 
    CUSTOMERNAME 
FROM customer 
WHERE 
    true = (
        case
            when UPPER(CODE) = 'ALL' then true
            else false
        end
    )
    OR 
    CUSTOMER_CODE= CODE
$$;

Result:

select * from table(test_fun('1'));
+---------------+--------------+
| CUSTOMER_CODE | CUSTOMERNAME |
|---------------+--------------|
|             1 | customer 1   |
+---------------+--------------+

select * from table(test_fun('2'));
+---------------+--------------+
| CUSTOMER_CODE | CUSTOMERNAME |
|---------------+--------------|
|             2 | customer 2   |
+---------------+--------------+

select * from table(test_fun('3'));
+---------------+--------------+
| CUSTOMER_CODE | CUSTOMERNAME |
|---------------+--------------|
+---------------+--------------+

select * from table(test_fun('all'));
+---------------+--------------+
| CUSTOMER_CODE | CUSTOMERNAME |
|---------------+--------------|
|             1 | customer 1   |
|             2 | customer 2   |
+---------------+--------------+
user_98793790
Related