Hi!

You should suspend every records like this:

DO
begin
IF (PI_KEY_IN = 0) THEN
EXCEPTION ROOT_CAT_NODE_DELETE;

SUSPEND;
end


András


From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Thursday, May 3, 2018 10:33 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure



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
&nb sp; 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 follo ws...

>>>

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,
&n bsp; 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,
&n bsp; :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...



__________ Information from ESET Mail Security, version of virus signature database 17326 (20180503) __________

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]