Re: Path of entry in hierarchical table

[Glenn Thomas Hvidsten]

Thanks for that link. It seems I forgot a "DO" at the end of my WHILE.
That changed the error message into "you cannot return a result set from a function" (or something like that).

I then changed the inner SELECT statement to a "SELECT INTO" and now it seems to work the way I want it to. Thanks a lot for you help! :D

Here's the final SQL:


DELIMITER //
CREATE FUNCTION ItemPath (itemID varchar(36))
RETURNS varchar(4096) DETERMINISTIC
BEGIN

SET @id := itemID, @path := '/';

WHILE @id != '00000000-0000-0000-0000-000000000000' DO
SELECT ParentID, CONCAT('/', Name, @path) INTO @id, @path FROM Items WHERE ID = @id;
END WHILE;

RETURN @path;

END;
//
DELIMITER ;