[SOLVED] using if else in mysql user defined function

using if else in mysql user defined function

This is my query that basically takes two numbers that adds them and multiplies the sum by 10

DELIMITER $$
CREATE FUNCTION tot(a int(4),b INT(4)) RETURNS INT(4)
BEGIN
RETURN ROUND((a+b)*10/9);
END $$
DELIMITER ;

everything is working fine , but I was wondering if there was a way I could add an IF ELSE that checks if any of of the values entered is null and if so the null value is assigned a value of zero

I've tried this but I'm getting an error

DELIMITER $$
CREATE FUNCTION tot(a int(4),b INT(4)) RETURNS INT(4)
BEGIN
IF (a = "") then
a=0;
ELSE IF (b = "")
b=0;
ELSE
END IF;
RETURN ROUND((a+b)*10/9);
END $$
DELIMITER ;

Solution

I was wondering if there was a way I could add an IF ELSE that checks if any of of the values entered is null and if so the null value is assigned a value of zero

You can just use coalesce() to assign a default to null parameters:

DELIMITER $$
CREATE FUNCTION tot(a int(4),b INT(4)) RETURNS INT(4)
BEGIN
    RETURN ROUND((COALESCE(a, 0) + COALESCE(b, 0)) * 10 / 9);
END $$
DELIMITER ;