Hello...

I have been in the process of converting my application's SQL Server T-SQL code to Firebird PSQL procedures.  So far so good until I got to the one where I built a Firebird procedure with a recursive CTE in it.

The following code is an exact match to my original SQL Server T-SQL code...

>>>

    WITH RECURSIVE HIERARCHY_TABLE AS
    (
        SELECT RCN1.CN_KEY,
               RCN1.CN_PARENT_KEY
            FROM RI_CATEGORY_NODES RCN1
            WHERE RCN1.CN_KEY = 3
            UNION ALL
                SELECT RCN2.CN_KEY,
                       RCN2.CN_PARENT_KEY
                    FROM RI_CATEGORY_NODES RCN2
                    JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY =     
                                             HIERARCHY_TABLE.CN_KEY
    )
    SELECT * FROM HIERARCHY_TABLE;
<<<

When run in my Firebird DB Manager within a query script-screen against the two records in the table, it returns both records as it should.

The records are setup as follows...

>>>

record #1   CN_KEY = 3,  CN_PARENT_KEY = 0
                 (a parent key of 0 means that this is the top-most record in the hierarchy)

record #2   CN_KEY = 4,  CN_PARENT_KEY = 3
                 (child record to to record #1)
<<<

The result then from this test is that the Firebird PSQL code produces the same exact result as my SQL Server's T-SQL code when run.

The problem I am finding however, is that when I run my Firebird PSQL code above within a procedure, it only returns record #2, the child record, instead of both records.

My Firebird PSQL procedure is as follows...

>>>

CREATE PROCEDURE SP_GET_CAT_CHILD_NODES_BYKEY(
  PI_KEY_IN BIGINT NOT NULL)
RETURNS(
  PI_KEY_OUT BIGINT,
  PI_PARENT_KEY_OUT BIGINT)
AS
BEGIN
  FOR
    WITH RECURSIVE HIERARCHY_TABLE AS
    (
        SELECT RCN1.CN_KEY,
               RCN1.CN_PARENT_KEY
            FROM RI_CATEGORY_NODES RCN1
            WHERE RCN1.CN_KEY = :PI_KEY_IN
            UNION ALL
                SELECT RCN2.CN_KEY,
                       RCN2.CN_PARENT_KEY
                    FROM RI_CATEGORY_NODES RCN2
                    JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY =
                                             HIERARCHY_TABLE.CN_KEY
    )
    SELECT CN_KEY,
           CN_PARENT_KEY
        FROM HIERARCHY_TABLE
        INTO :PI_KEY_OUT,
             :PI_PARENT_KEY_OUT
  DO   

  IF (PI_KEY_IN = 0) THEN
      EXCEPTION ROOT_CAT_NODE_DELETE;
   
  SUSPEND;
END;
<<<

Can anyone explain why my procedure is not returning the expected number of records?

Thank you...