🚀 go-pugleaf

The following stored proc is from a program that stores notes in a
hierarchical structure of categories. This proc is called to populate a
TTreeview that displays the categories.

COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE "CATEGORY_TREE" 
RETURNS
(
  "CATEGORY" VARCHAR(40),
  "CATEGORY_ID" INTEGER,
  "PARENT_CATEGORY_ID" NUMERIC(18, 0),
  "CATEGORY_LEVEL" INTEGER
)
AS
BEGIN EXIT; END ^


ALTER PROCEDURE "CATEGORY_TREE" 
RETURNS
(
  "CATEGORY" VARCHAR(40),
  "CATEGORY_ID" INTEGER,
  "PARENT_CATEGORY_ID" NUMERIC(18, 0),
  "CATEGORY_LEVEL" INTEGER
)
AS
  DECLARE VARIABLE I INTEGER;
BEGIN
  /* SELECT THE FIRST LEVEL CATEGORIES */
  FOR SELECT C.CATEGORY, C.CATEGORY_ID, C.PARENT_CATEGORY_ID, 0
    FROM CATEGORY C
    WHERE C.PARENT_CATEGORY_ID IS NULL
    INTO :CATEGORY, :CATEGORY_ID, :PARENT_CATEGORY_ID, :CATEGORY_LEVEL
  DO
  BEGIN
    /* RETURN THE ROW */
    SUSPEND;

    /* FOR EACH TOP LEVEL ROW CALL THIS PROCEDURE AGAIN TO GET THE
ROW'S CHILDREN */
    FOR SELECT P.CATEGORY, P.CATEGORY_ID, P.PARENT_CATEGORY_ID,
CATEGORY_LEVEL
      FROM CATEGORY_SUBTREE(:CATEGORY_ID, :CATEGORY_LEVEL) P
      INTO :CATEGORY, :CATEGORY_ID, :PARENT_CATEGORY_ID, :CATEGORY_LEVEL
    DO
    BEGIN
      /* RETURN THE CHILD ROW */
      SUSPEND;
    END
  END
END
 ^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;

-- 
Bill Todd (TeamB)