mysql-recursieve-stored-function

CREATE TABLE  categories (
  `categoryid` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  `parentid` int(10) unsigned default NULL,
  PRIMARY KEY  (`categoryid`),
  KEY `FK_categories_1` (`parentid`),
  CONSTRAINT `FK_categories_1` FOREIGN KEY (`parentid`) REFERENCES `categories` (`categoryid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER $$

DROP FUNCTION IF EXISTS catname $$
CREATE FUNCTION catname(arg_categoryid INT) RETURNS varchar(100) CHARSET utf8
BEGIN
  DECLARE currentCategoryId INT;
  DECLARE categoryname VARCHAR(100);
  DECLARE padder VARCHAR(20) DEFAULT '';

  SET currentCategoryId = arg_categoryid;
  SELECT name INTO categoryname FROM categories WHERE categoryid = arg_categoryid;

  catloop : LOOP
    SELECT parentid INTO currentCategoryid FROM categories WHERE categoryid = currentCategoryId;

    IF currentCategoryId IS NULL THEN LEAVE catloop; END IF;

    SET padder = CONCAT(padder,'-');
  END LOOP catloop;


  RETURN CONCAT(padder,' ',categoryname);
END $$

DELIMITER ;

INSERT INTO `categories` (`categoryid`, `name`, `parentid`) VALUES (3, 'level 1 - test', NULL),
(4, 'level 1 - test 2', NULL),
(5, 'level 1 - test 3', NULL),
(6, 'level 2 - test', 4),
(7, 'level 2 - test', 4),
(8, 'level 3 - test', 6),
(9, 'level 3 - test 2', 6);