using if else in mysql user defined function

Question

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 ;

`ELSE IF` should only be used when the conditions are mutually exclusive. What if both `a` and `b` are null? — Barmar, Sep 10 '20 at 21:22
Why do you think `ELSE a=a;` is necessary? Why would you need to assign a variable to itself. — Barmar, Sep 10 '20 at 21:23
I just put the a=a by mistake but I've edited it out and for the both b=" "and a='' " I had not thought of that but GMB has provide a perfect solution with coalesce() — Luis, Sep 10 '20 at 21:30

score 2 · Accepted Answer · answered Sep 10 '20 at 21:08

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 ;

using if else in mysql user defined function

1 Answers1