Jujunol Jujunol - 7 months ago 10
SQL Question

MySQL - Function keeps returning null

I'm trying to create a MySQL function that will store the employee's time on projects and store it into

v_emptime
and then use that variable to calculate a total expense, like so:

delimiter //

create function F_COUNT_EDUCATION (p_empno char(6)) returns decimal(10, 2)
begin

declare v_emptime decimal(5, 2);
declare output decimal(10, 2);

select sum(emptime) # There are multiple records for some, using sum() for total time
into @v_emptime
from empprojact
where empno = p_empno;

# if I return v_emptime here it will be null

select (@v_emptime * comm + salary + bonus) expense
into @output
from employee
where empno = p_empno;

return @output;

end //

delimiter ;


However, the problem is that
output
will always return null. I've checked the data countless times and the data all have values. Which means I'm probably overlooking something very simple. Any help or insight would be appreciated, thanks!

Answer

Try this : Change name of "@output" variable. And change query this :

select  @v_emptime = sum(emptime) 
from empprojact
where empno = p_empno;

And :

select @newVariableName = (@v_emptime * comm + salary + bonus) expense
from employee where empno = p_empno;
Comments