CREATE DEFINER=`root`@`%` FUNCTION `queryChildrenSuiteIds`(suiteId varchar(20)) RETURNS varchar(4000) CHARSET utf8
BEGIN
DECLARE childSuiteIds VARCHAR(4000);
DECLARE parentSuiteIds VARCHAR(4000);
SET childSuiteIds='';
SET parentSuiteIds = CAST(suiteId AS CHAR);
WHILE parentSuiteIds IS NOT NULL DO
SET childSuiteIds= CONCAT(parentSuiteIds, ',', childSuiteIds);
SELECT GROUP_CONCAT(dept_code) INTO parentSuiteIds FROM ehr_org.org_department WHERE FIND_IN_SET(parent_dept_code, parentSuiteIds)>0;
END WHILE;
RETURN childSuiteIds;
END