如何检查MySQL存储函数返回空结果的原因？

Question

DELIMITER $$
CREATE FUNCTION `monthtargetbyuser`(`userId` BIGINT,month VARCHAR(11),year VARCHAR(11)) RETURNS int(11)

BEGIN 

  DECLARE target,da BIGINT;
  set da = year-month;

  SET target = (SELECT ifnull(user_target.monthly_target,0) as monthly_target from user_target left join users on users.id=user_target.user_id where date_format(users.doj,'%Y-%m') <= 'da' and user_target.year=year and year(users.doj)>0 and users.locked !=1 and users.id =userId );  


  RETURN target;
END

While executing the MySQL query given in the function, it returns an empty result or zero rows. But while executing the stored function, it gives incorrect result, i.e, it returns a value. How do I check that the MySQL function returns an empty result?

Answer 1

Some smart person will probably give a better answer but what worked for me was to add a check at the end of the function, to see if the variable was null and if it was then set it to a value such as Unknown. That way you always get a value returned from the function, even when no rows are returned from your query, eg:

  SET target = (SELECT ifnull(user_target.monthly_target,0) as monthly_target from user_target left join users on users.id=user_target.user_id where date_format(users.doj,'%Y-%m') <= 'da' and user_target.year=year and year(users.doj)>0 and users.locked !=1 and users.id =userId );  

  IF (target IS NULL) THEN
        SET target = "Unknown";
  END IF;

  RETURN target;