Syntax error creating function in Snowflake

Question

I am unable to fix a syntax error that I keep getting when trying to create a function in Snowflake. This works fine when I create a procedure, but whenever I try to create a function with a variable declaration, I keep getting the error below. I want this to be a function since it only returns a single, scalar value.

Syntax error: unexpected 'l_emp_excess_balance'.t

Here is the code:

CREATE OR REPLACE FUNCTION MDT_PRESDEV_DB_ADMIN.PERS.begin_bal_emp_excess_comp(psid VARCHAR)
RETURNS NUMBER
AS
$$
DECLARE
  l_emp_excess_balance NUMBER;
BEGIN
  SELECT COALESCE(v.peec_excess_leave_hrs, 0)
  INTO l_emp_excess_balance
  FROM MDT_PRESDEV_DB_ADMIN.pers_excess_xmpt_comp_leave v
  WHERE v.peec_ps_id = psid
    AND v.peec_system = 'PERS'
    AND v.peec_year = (
      SELECT MAX(m.peec_year)
      FROM MDT_PRESDEV_DB_ADMIN.pers_excess_xmpt_comp_leave m
      WHERE m.peec_system = 'PERS'
    );
  RETURN COALESCE(l_emp_excess_balance, 0);
END;
$$;

I tried creating a procedure instead of a function. That worked, but I would much rather this just be a function.

The DECLARE / BEGIN / END / RETURN are procedure statements. A SQL Function would just have a SQL expression or SELECT statement (in quotes or $$) after the AS keyword — Fred
– Fred, Commented Aug 7 at 20:18
Thank you Fred. I am new to coding functions and procedures in SQL, so this is part of a learning experience for me. This is part of an effort to migrate the data source for Jasper reports from Oracle to Snowflake. So im doing my best to recode the Oracle functions for Snowflake. — Thomas_
– Thomas_, Commented Aug 7 at 20:22

Dale K · Accepted Answer · 2025-08-10 06:52:49Z

2

Scalar SQL UDFs expects an expression and not Scripting block DECLARE BEGIN END:

CREATE OR REPLACE FUNCTION MDT_PRESDEV_DB_ADMIN.PERS.begin_bal_emp_excess_comp(psid VARCHAR)
RETURNS NUMBER
AS
$$
  SELECT COALESCE(v.peec_excess_leave_hrs, 0)
  FROM MDT_PRESDEV_DB_ADMIN.pers_excess_xmpt_comp_leave v
  WHERE v.peec_ps_id = psid
    AND v.peec_system = 'PERS'
    AND v.peec_year = (
      SELECT MAX(m.peec_year)
      FROM MDT_PRESDEV_DB_ADMIN.pers_excess_xmpt_comp_leave m
      WHERE m.peec_system = 'PERS'
    )
$$;

Side note: if no row is found due to filtering then the query will return NULL even though COALESCE is used.

CREATE OR REPLACE FUNCTION test()
RETURNS NUMBER
AS
$$
  SELECT COALESCE(col, 0)
  FROM (VALUES (1)) AS sub(col)
  WHERE 1=2
$$;

SELECT test();
-- null

To ensure that there is always at least one row returned an aggregate function before COALESCE can be used. Aggregate function should be chosen carefully especially if it is possible to get more than a single row after filtering:

CREATE OR REPLACE FUNCTION test()
RETURNS NUMBER
AS
$$
  SELECT COALESCE(MIN(col), 0)
  FROM (VALUES (1)) AS sub(col)
  WHERE 1=2
$$;

SELECT test();
-- 0

Snowflake Scripting user-defined functions (UDFs) are going to enable Scripting block for UDFs(9.23 Release Notes: Preview)

edited Aug 10 at 6:52

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

answered Aug 7 at 20:10

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas_ Aug 7 at 20:18

Yes this answers my question! Thank you very much for the answer and for the additional information.

Collectives™ on Stack Overflow

Syntax error creating function in Snowflake

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related