I'm likely to get scolded by Helen as being off topic, as I am not sure
this is a lack of understanding about firebird procedure language, or my
lack of mental capacity on Monday to see the problem with the recursive
logic. But, here goes...

for reference...
Create table Category
(ID int not null primary key,
description varchar(50) not null,
parent_ID int;)

alter table category add constraint FK foreign key (parent_ID)
references Categry (ID);


now the question.. I want a sp in which I will pass the ID. It returns
a row for the ID passed in, and a row for every parent up the line until
parent_ID is null..

This returns only the row I passes in, whats wrong with it?

CREATE PROCEDURE SP_CAT_PARENT (
In_ID INTEGER)
RETURNS (
Out_ID INTEGER,
DESCRIPTION VARCHAR(50),
PARENT_ID INTEGER)
AS
BEGIN
--HAS EXACTLY ZERO OR ONE PARENT
SELECT C.ID, C.DESCRIPTION, C.PARENT_ID
FROM CATEGORY C
WHERE C.ID = :In_ID
into :Out_ID, :DESCRIPTION, :PARENT_ID;

-- THEN LOOK FOR ITS PARENT

if (:PARENT_ID IS NOT NULL) then -- WE FOUND at least one row, AND it
has a parent
BEGIN
--return the row we found
SUSPEND;
-- CALL RECURSIVELY WITH THE PARENT_ID
SELECT O_ID, DESCRIPTION, PARENT_ID
FROM SP_CAT_PARENT(:PARENT_ID)
INTO :Out_Id, :DESCRIPTION, :PARENT_ID;
END
ELSE --suspend the last parent
SUSPEND;
END


I know its something simple, but try not to be too cruel will ya?

TIA
Mitch