例子

一直就没有什么好的解决办法。
DROP TABLE IF EXISTS `item_category`;
CREATE TABLE `item_category` (
`id` int(11) NOT NULL auto_increment,
`catId` int(11) default NULL,
`parentId` int(11) default NULL,
PRIMARY KEY (`id`)
);

INSERT INTO `item_category` VALUES (1,1,0);
INSERT INTO `item_category` VALUES (2,2,1);
INSERT INTO `item_category` VALUES (3,3,1);
INSERT INTO `item_category` VALUES (4,4,2);
INSERT INTO `item_category` VALUES (5,5,3);

DELIMITER //
drop procedure if exists iterative//
CREATE PROCEDURE iterative(iid bigint(20),layer bigint(20))
BEGIN
declare tid bigint(20) default -1;
declare cur1 CURSOR FOR select catId from item_category where parentId=iid;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tid = null;
if layer > 0 then
OPEN cur1;
FETCH cur1 INTO tid;
WHILE(tid is not null)
DO
insert into tmp_table values(tid);
call iterative(tid,layer-1);
FETCH cur1 INTO tid;
END WHILE;
end if;
END;//
DELIMITER ;

call findLChild(1,2);

/*
2
4
3
5
*/

------解决方案--------------------

我用的是ms-sql2008