mssql树状目录遍历10层下属子帐号

//主过程 pro_show_childLst

//参数 rootName VARCHAR(30)

BEGIN
DROP TEMPORARY TABLE IF EXISTS tmpLst;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst(sno INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(30),depth INT);
CALL pro_cre_childlist(rootName,0);
SELECT * from tmpLst;
END

 

 

 

//子过程 pro_cre_childlist

//参数 rootName VARCHAR(30),nDepth INT

BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b VARCHAR(255);
DECLARE cur1 CURSOR FOR SELECT task001_users.username FROM task001_users WHERE upline=rootName;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET max_sp_recursion_depth=12;
INSERT INTO tmpLst VALUES (NULL,rootName,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL pro_cre_childlist(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END