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
– Barmar, Commented Sep 10, 2020 at 21:22
Why do you think ELSE a=a; is necessary? Why would you need to assign a variable to itself. — Barmar
– Barmar, Commented Sep 10, 2020 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
– Luis, Commented Sep 10, 2020 at 21:30

GMB · Accepted Answer · 2020-09-10 21:08:59Z

2

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 ;

answered Sep 10, 2020 at 21:08

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using if else in mysql user defined function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related