CREATE PROCEDURE proc_delete_department(IN rootid int)
begin 
      declare _level int DEFAULT 0;      
      DROP TABLE IF EXISTS temptab;      
      create table temptab(id int, level int);               
      insert into temptab(id, level) values(rootid, _level);
          
      REPEAT            
         insert into temptab(id, level) 
         select root.id, _level+1 
                from department root, temptab sub 
                where root.parentId= sub.id and sub.level = _level; 
         set _level = _level + 1;   
         until ROW_COUNT() = 0        
      end REPEAT;
      delete from department where  id in (select id from temptab); 
      DROP TABLE IF EXISTS temptab;            
end;