In MySQL stored procedures check if a local variable is null

Question

With the following example stored procedure

DECLARE Variable DOUBLE;
DECLARE Variable2 DOUBLE;
SELECT Something FROM Somewhere INTO Variable;

SELECT Something FROM SomewhereElse INTO Variable 2;

SELECT (Variable + Variable2);

If either Variable or Variable2 are NULL then the final SELECT will return null, what I would like is that if they are null they should be converted into 0.00 before the final SELECT, how do you do this? I already tried adding

SELECT 0.00 INTO Variable WHERE Variable IS NULL;

just about the final select but that didn't work.

Answer 1

SELECT COALESCE(variable, 0) + COALESCE(variable2, 0)

Answer 2

Quassnoi's correct, but it's even simpler (and a bit faster) to just coalesce the result:

 SELECT coalesce( Variable + Variable2, 0 );

This works because for almost all operators, any null operand will make the operation null:

   select 1 + null ; -- null
   select null + 1 ; -- null
   select null + null ; -- null

In the expression: SELECT coalesce( Variable + Variable2, 0 ); the result of the addition expression Variable + Variable2 is coalesce first argument; if that's null, coalesce returns its second argument, 0, otherwise it returns the (non-null) value of its first argument, which is the sum we want.

The few operators and functions that don't return null for a null operand are those designed to work on nulls: coalesce, is null, is not null.

As kristof notes below, the value of the select expression is different using Quassnoi's expression, if only one variable is null: his will return the value of the non-null variable if one is null; mine will return zero if either variable is null. Which is "right" depends on your intent.

Answer 3

if you want each variable null converted to 0 use the solution posted by Quassnoi

SELECT COALESCE(variable, 0) + COALESCE(variable2, 0)

if you want to have 0 if either variable is null then use

SELECT COALESCE(variable + variable2, 0)