Unable to run store procedure


  • 0
    C
    drop procedure if exists test;
    create procedure test()
    begin
        declare name_txt varchar(100);
        declare id_txt int;
        declare loop_cnt int;
        declare col_cnt int;
        declare cursor_tb cursor for select Id, Name from Department;
    
        set loop_cnt = 0; 
        set @tmp_sql = '';
        select count(1) into col_cnt from Department;
        open cursor_tb;
        while loop_cnt < col_cnt
        do 
        fetch cursor_tb into id_txt,name_txt; 
    	IF loop_cnt <> 0 
        THEN
        set @tmp_sql = concat(@tmp_sql,' union ');
        END IF;
        set @tmp_sql = concat(@tmp_sql,'select * from (select ''',name_txt);
        set @tmp_sql = concat(@tmp_sql,''' as Department, Name as Employee, Salary from Employee where DepartmentId=');
        set @tmp_sql = concat(@tmp_sql,id_txt);
        set @tmp_sql = concat(@tmp_sql,' order by Salary DESC limit 3 ) as '); 
        set @tmp_sql = concat(@tmp_sql,name_txt);
        set @tmp_sql = concat(@tmp_sql,'_tmp ');
        
        set loop_cnt = loop_cnt +1;
        end while;
        prepare stmt3 from @tmp_sql;
        execute stmt3;
        close cursor_tb;
    end;
    call test();

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.