Snowflake: PRIOR keyword is missing in Connect By statement

Question

I am trying to implement a stored procedure to return the result set of a CONNECT BY query in Snowflake

create or replace procedure test(email varchar(100))
RETURNS TABLE (email_address varchar(100))
LANGUAGE SQL
AS
BEGIN
  let res RESULTSET := (WITH BASE AS (
            select 
                USER_ID
                , MANAGER_ID
                , EMAIL_ADDRESS
            from HIERARCHY 
            WHERE USER_ID <> MANAGER_ID
        ) 
        SELECT EMAIL_ADDRESS
        FROM BASE
        START WITH EMAIL_ADDRESS = :email
        CONNECT BY USER_ID = PRIOR MANAGER_ID
    );
  RETURN TABLE(res);
END
;

But am receiving the error: "PRIOR keyword is missing in Connect By statement". The PRIOR keyword is clearly in my connect by statement. Runs outside of stored procedure. Is there something else in here that I am missing?

I have tried the query outside of the stored procedure and would expect a list of email addresses:

EMAIL_ADDRESS
[email protected]
[email protected]

Answer 1

Workaround using EXECUTE IMMEDIATE and parametrized query:

create or replace procedure test(email varchar(100)) 
RETURNS TABLE (email_address varchar(100)) 
LANGUAGE SQL 
AS
DECLARE 
   query VARCHAR := '(WITH BASE AS ( select  USER_ID , MANAGER_ID , EMAIL_ADDRESS 
                                     from HIERARCHY  
                                     WHERE USER_ID <> MANAGER_ID )  
                      SELECT EMAIL_ADDRESS 
                      FROM BASE 
                      START WITH EMAIL_ADDRESS = ? 
                      CONNECT BY USER_ID = PRIOR MANAGER_ID )';
    res RESULTSET;
BEGIN 
  res := (EXECUTE IMMEDIATE :query USING (email));
  RETURN TABLE(res);
END ;

Input data:

CREATE OR REPLACE TABLE HIERARCHY AS
SELECT 1 AS USER_ID, 2 AS MANAGER_ID, 'a' AS EMAIL_ADDRESS;

Call:

CALL TEST('a');
-- a