[SOLVED] Snowflake function parameters with wildcard in where clause

Snowflake function parameters with wildcard in where clause

Do you know why the following function call wont work using wildcards inside the parameter?

CREATE OR REPLACE FUNCTION ABC("CUSTCODE" VARCHAR, "CUSTNAME" VARCHAR)
RETURNS TABLE ("CUSTOMER_CODE" VARCHAR, CUSTOMERNAME VARCHAR)
LANGUAGE SQL
AS $$                                  
   SELECT  CUSTOMER_CODE, CUSTOMERNAME FROM DIM_CUSTOMER
    WHERE  CUSTNAME is null AND CUSTOMER_CODE ILIKE '%CUSTCODE%'
$$;

I'm getting no errors but also no records found

It seems like its not liking the ILIKE '%CUSTCODE%' bit.

Solution

You have string quotes ' inside the code block, but the code block is marked with single quotes. So ether swap to double dollars $$

CREATE OR REPLACE FUNCTION ABC("CUSTCODE" VARCHAR)
RETURNS TABLE ("CUSTOMER_CODE" VARCHAR, CUSTOMERNAME VARCHAR)
LANGUAGE SQL
AS $$                                  
   SELECT  CUSTOMER_CODE, CUSTOMERNAME FROM DIM_CUSTOMER
    WHERE  CUSTOMER_CODE ILIKE '%CUSTCODE%'
$$;

OR use a double single in the inside..

CREATE OR REPLACE FUNCTION ABC("CUSTCODE" VARCHAR)
RETURNS TABLE ("CUSTOMER_CODE" VARCHAR, CUSTOMERNAME VARCHAR)
LANGUAGE SQL
AS '                                  
   SELECT  CUSTOMER_CODE, CUSTOMERNAME FROM DIM_CUSTOMER
    WHERE  CUSTOMER_CODE ILIKE ''%CUSTCODE%''
';