IF ELSE with NULL values mysql

Question

im having problems creating a function that must return the amount of goals of a team as a home and away First it sums all local goals, and then it sums all visitor goals, and saves into variables, but the problem is, i want to know what can i do if there is NO data, i mean, what can i do when it returns NULL, i tried with IF ELSE but still not working, here is the code:

CREATE DEFINER=`root`@`localhost` FUNCTION `vgoles`(`veq` int) RETURNS int(11)
BEGIN
#Routine body goes here...
DECLARE vgloc INT;
DECLARE vgvis INT;
DECLARE vgoles INT;
SELECT SUM(gloc) INTO @vgloc FROM partidos WHERE eqloc=@veq;
SELECT SUM(gvis) INTO @vgvis FROM partidos WHERE eqvis=@veq;
IF @vgloc = NULL THEN
SET @vgloc = 0;
END IF;
IF @vgvis = NULL THEN
SET @vgvis = 0;
END IF;
SET @vgoles=@vgloc+@vgvis;
RETURN @vgoles;
END

Thanks and have a nice day

Kumar V · Accepted Answer · 2014-02-22 10:02:23Z

4

The IF @vgloc = NULL THEN does not work as you expect because you can't check NULL with equality (=). NULL is a special value that is not equal to anything, not even to itself.

SELECT (3 = NULL)          --yields--> NULL
SELECT NOT (3 = NULL)      --yields--> NULL
SELECT (NULL = NULL)       --yields--> NULL
SELECT (NULL <> NULL)      --yields--> NULL
SELECT (NULL IS NULL)      --yields--> TRUE

To check for NULL value, you need: IF @vgloc IS NULL THEN.

But you can also use COALESCE() function for further simplicity:

SELECT COALESCE(SUM(gloc),0) INTO @vgloc FROM partidos WHERE eqloc=@veq;
SELECT COALESCE(SUM(gvis),0) INTO @vgvis FROM partidos WHERE eqvis=@veq;

edited Feb 22, 2014 at 10:02

Kumar V

8,8289 gold badges44 silver badges60 bronze badges

answered Oct 23, 2011 at 0:47

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

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

1 Comment

Enrique Benitez Over a year ago

It worked! Thanks so much! PD: Since is a function, i cant put an '@' on the parameter variable, that was the other problem, but now it works very good!

Collectives™ on Stack Overflow

IF ELSE with NULL values mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related