-- Collection of code snippets by Arne Vajhøj 
-- (likely posted to comp.os.vms/INFO-VAX, INFO-TPU, MACRO32, eksperten.dk, newz.dk, LinkedIn or other place sometime between 1990 and now) 
CREATE TABLE stuff (id INTEGER PRIMARY KEY,name VARCHAR(50),parent INTEGER);

INSERT INTO stuff VALUES(1,'tools',0);
INSERT INTO stuff VALUES(2,'hammer',1);
INSERT INTO stuff VALUES(3,'screwdriver',1);
INSERT INTO stuff VALUES(4,'fruits',0);
INSERT INTO stuff VALUES(5,'yellow fruits',4);
INSERT INTO stuff VALUES(6,'green fruits',4);
INSERT INTO stuff VALUES(7,'apple',6);
INSERT INTO stuff VALUES(8,'banana',5);
INSERT INTO stuff VALUES(9,'grape',6);

DELIMITER //
CREATE PROCEDURE lookup(_name varchar(50))
BEGIN
    DECLARE _id INTEGER;
    DECLARE _parent INTEGER;
    SELECT id,parent INTO _id,_parent FROM stuff WHERE name = _name;
    SET @sql = CONCAT('SELECT * FROM stuff WHERE id IN (', CAST(_id AS CHAR));
    WHILE _parent <> 0 DO
        SET @sql = CONCAT(@sql,','); 
        SET @sql = CONCAT(@sql,CAST(_parent AS CHAR)); 
        SET _id = _parent;
        SELECT parent INTO _parent FROM stuff WHERE id = _id;
    END WHILE;
    SET @sql = CONCAT(@sql,')');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

CALL lookup('grape');
CALL lookup('hammer');

DROP PROCEDURE lookup;

DROP TABLE stuff;
