CODEWITHSUNDEEP
functionparameter-passingsnowflake-cloud-data-platform
Liem Nguyen
Liem Nguyen

Reputation: 99

Declaring Dates and Nulls in function calls

When I call a function eg

SELECT  * FROM TABLE(FN_ABC(NULL::DATE , '2022-04-07'::DATE ))

Snowflake forces me to bind the parameters Im passing to a datatype eg DATE and for all NULLs as above.

Is there a way to avoid binding them? I've already declared them as DATES in my function

ie simply just call it without the datatypes

SELECT  * FROM TABLE(FN_ABC(NULL, '2022-04-07'))

the reason is these parameters are being passed from a webpage as just NULL or 2022-04-07

Thank you

Upvotes: 0

Views: 94

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25968

your sql is not two dates, it's an undefined type, and a string/text/varchar.

select
    null as a
    ,'2022-04-07' as b
    ,system$typeof(a) as t_a
    ,system$typeof(b) as t_b
    ,a::date as d_a
    ,b::date as d_b
    ,system$typeof(d_a) as t_da
    ,system$typeof(d_b) as t_db
A B T_A T_B D_A D_B T_DA T_DB
null 2022-04-07 NULL[LOB] VARCHAR(10)[LOB] null 2022-04-07 DATE[SB4] DATE[SB4]

Thus using those values gives you an error:

001044 (42P13): SQL compilation error: error line 1 at position 21

Invalid argument types for function 'FN_ABC': (NULL, VARCHAR(10))

But if you already have DATE values, there is not problem:

with data as (
  select 
    null as a
    ,'2022-04-07' as b
    ,system$typeof(a) as t_a
    ,system$typeof(b) as t_b
    ,a::date as d_a
    ,b::date as d_b
     ,system$typeof(d_a) as t_da
    ,system$typeof(d_b) as t_db
  )
SELECT 
    f.*
FROM data as d
,table(fn_abc(d.d_a, d.d_b)) f;

runs, just fine

X Y
null 2022-04-07

Upvotes: 1

Related Questions