delimiter &&

create PROCEDURE findProjectDetailsByProjectId(in p_userId int)

BEGIN

   DECLARE done INT DEFAULT 0;

   DECLARE d_projectId int;

     DECLARE projectLength int;

     DECLARE c_projectId VARCHAR(100) default '';

   DECLARE findProject_cur CURSOR for

            select t1.projectId

                 from (

                    select * from  odao_admin.admin_login_user where dept_id in(

                        select deptId from odao_admin.admin_role_department where roleId  = (select role_id from odao_admin.admin_role_user where user_id = p_userId )

                    )) t,odao_wpf_project t1 where t.user_id = t1.userId and t1.`status` = 1  ;

      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN findProject_cur;

              findProject_loop:LOOP

                     FETCH findProject_cur INTO d_projectId;

                        IF done THEN

                                LEAVE findProject_loop;

                        END IF;

                        set c_projectId = CONCAT(c_projectId,CONCAT(d_projectId,',')) ;

              END LOOP;

        CLOSE findProject_cur;

        set projectLength = (select char_length(c_projectId));

        set c_projectId = (select left(c_projectId,char_length(c_projectId)-1)  );

        set @vsql = CONCAT('select t2.*,t3.* from odao_wpf_board_task t2,(select t.boardId,boardName,t1.* from odao_wpf_board t,(select boardClassId,boardClassName from odao_wpf_boardclass where projectId in(',c_projectId,') ORDER BY orderId  ) t1 where t.boardClassId = t1.boardClassId ) t3 where t2.boardId = t3.boardId' );

      

        select @vsql;

        prepare stmt from @vsql;

     EXECUTE stmt;     

     deallocate prepare stmt;    

    

    

    

END

&&

delimiter ;