CREATE FUNCTION getChildList(rootId INT)

RETURNS varchar(1000)

BEGIN

DECLARE sChildList VARCHAR(1000);

DECLARE sChildTemp VARCHAR(1000);

SET sChildTemp =cast(rootId as CHAR);

WHILE sChildTemp is not null DO

IF (sChildList is not null) THEN

SET sChildList = concat(sChildList,',',sChildTemp);

ELSE

SET sChildList = concat(sChildTemp);

END IF;

SELECT group_concat(id) INTO sChildTemp FROM user_role where FIND_IN_SET(parentid,sChildTemp)>0;

END WHILE;

RETURN sChildList;

END;