SQL Schleifen Problem

[joachimd]

Tabelle erzeugen:
CREATE TABLE cat(id AutoInc, parent_cat Integer, text Char(10 )) IN DATABASE; Rekursive Prozedur, um alle Unter-Kategorien zu bekommen:

SQL-Code:

CREATE PROCEDURE subcat(parent_cat Integer, id Integer OUTPUT) 
BEGIN 
  declare @c cursor;
  declare @parent_cat integer;
  @parent_cat=(select parent_cat from __input);
  if @parent_cat is null
    then open @c as select id from cat where parent_cat is null;
    else open @c as select id from cat where parent_cat = @parent_cat;
  endif;
  while fetch @c do
    insert into __output values (@c.id); 
   insert into __output select * from (execute procedure subcat(@c.id)) a;
  endwhile;
  close @c;
END;

Verwendung (gib alle Unter-Kategorien von Eintrag 1):

SQL-Code:

select c.* from cat c
inner join (EXECUTE PROCEDURE subcat(1)) a
  on c.id=a.id;