-- 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)
GO

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

CREATE FUNCTION lookupone(@parent INTEGER) RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @id INTEGER
    DECLARE @idlist VARCHAR(8000)
    SET @idlist = ''
    DECLARE c CURSOR FOR SELECT id FROM stuff WHERE parent = @parent
    OPEN c
    FETCH NEXT FROM c INTO @id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @idlist = @idlist + ',' + CAST(@id AS VARCHAR(9)) + dbo.lookupone(@id)
        FETCH NEXT FROM c INTO @id
    END
    CLOSE c
    DEALLOCATE c
    RETURN @idlist
END
GO

CREATE PROCEDURE lookup(@name varchar(50))
AS
BEGIN
    DECLARE @id INTEGER
    DECLARE @idlist VARCHAR(8000)
    DECLARE @sql VARCHAR(8000)
    SET @id = (SELECT id FROM stuff WHERE name = @name)
    SET @idlist = CAST(@id AS VARCHAR(9)) + dbo.lookupone(@id)
    SET @sql = 'SELECT * FROM stuff WHERE id IN (' + @idlist + ')'
    EXEC(@sql)
END
GO

EXEC lookup 'tools'
GO
EXEC lookup 'fruits'
GO

DROP PROCEDURE lookup
GO
DROP FUNCTION lookupone
GO

DROP TABLE stuff
GO
