MySQL Call a Stored Procedure inside a Function

Question

So I've been looking all over the place trying to see how I can call a function inside a stored procedure and I found this example here: http://forums.mysql.com/read.php?98,175470,175476#msg-175476.

This helped me structure my function to call the stored procedure, however I keep getting this error:

16:17:51    select regionUtilization(1,2) LIMIT 0, 1000 Error Code: 1415. Not allowed to return a result set from a function    0.000 sec

What I'm trying to do is call the following stored procedure and access the OUT variable. Then compare that to the inputted compare integer.

drop procedure if exists select_employeesByRegion_proc;
delimiter //
create procedure select_employeesByRegion_proc
(in search int, out result int)
    begin
        select t1.emp_id from (
        select employees.emp_id from branch
        inner join department
        on department.br_id = branch.br_id
        inner join employees
        on employees.dep_id = department.dep_id
        where branch.reg_id = search) as t1;
     set result:=FOUND_ROWS();
     end //
delimiter ;

Below is the function that I currently have.

drop function if exists regionUtilization;
delimiter //
create function regionUtilization(search int, compare int)
    returns boolean
begin
    DECLARE temp int;
    call select_employeesByRegion_proc(search, temp);
    if temp >= compare then 
        return true;
    else
        return false;
    end if;
end //
delimiter ;

I've also looked into separating out the two aspects of the stored procedure into separate procedures by having one count and the other return results, however this would still first require the procedure to select some data which would cause the same error as I'm already receiving.

Any suggestions on how to work around the result set error? I'm not returning the result set, I'm just using that result set to choose whether to return true or false in my function. Thanks in advance!

Answer 1

Thank you @Barmar for the answer. Yes I needed to use a cursor in my procedure to declare my function appropriately.

drop procedure if exists build_regionUtil_proc;
delimiter //
create procedure build_regionUtil_proc(in search int, inout result int)
    begin
        declare v_finished integer default 0;
        declare v_list int default 0;
        declare region_cursor cursor for
            select t1.emp_id from (
            select employees.emp_id from branch
            inner join department
            on department.br_id = branch.br_id
            inner join employees
            on employees.dep_id = department.dep_id
            where branch.reg_id = search) as t1;
        declare continue handler
            for not found set v_finished = 1;
        open region_cursor;
        get_results: loop
            fetch region_cursor into v_list;
            if v_finished = 1 then leave get_results;
            end if;
            set result = result + 1;
        end loop get_results;
        close region_cursor;
    end //
delimiter ;