DELIMITER $$
CREATE DEFINER=`arun`@`%` PROCEDURE `recursivesubtree`( in iroot int(100) , in ilevel int(110) , in locid int(101) )
BEGIN
DECLARE irows,ichildid,iparentid,ichildcount,done INT DEFAULT 0;
DECLARE cname VARCHAR(64);
SET irows = ( SELECT COUNT(*) FROM account WHERE parent_id=iroot and location_id=locid );
IF ilevel = 0 THEN
DROP TEMPORARY TABLE IF EXISTS _descendants;
CREATE TEMPORARY TABLE _descendants (
childID INT, parentID INT, name VARCHAR(64), childcount INT, level INT
);
END IF;
IF irows > 0 THEN
BEGIN
DECLARE cur CURSOR FOR
SELECT
f.account_id,f.parent_id,f.account_name,
(SELECT COUNT(*) FROM account WHERE parent_id=t.account_id and location_id=locid ) AS childcount
FROM account t JOIN account f ON t.account_id=f.account_id
WHERE t.parent_id=iroot and t.location_id=locid
ORDER BY childcount<>0,t.account_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur;
WHILE NOT done DO
FETCH cur INTO ichildid,iparentid,cname,ichildcount;
IF NOT done THEN
INSERT INTO _descendants VALUES(ichildid,iparentid,cname,ichildcount,ilevel );
IF ichildcount > 0 THEN
CALL recursivesubtree( ichildid, ilevel + 1 );
END IF;
END IF;
END WHILE;
CLOSE cur;
END;
END IF;
IF ilevel = 0 THEN
SET cname = (SELECT account_name FROM account WHERE account_id=iroot and location_id=locid );
SET @sql = CONCAT('SELECT CONCAT(REPEAT(CHAR(36),2*level),IF(childcount,UPPER(name),name))',
' AS ', CHAR(39),cname,CHAR(39),' FROM _descendants');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
END IF;
END$$
DELIMITER ;