IF… ELSE in MySQL.

How to use IF… ELSE in MySQL?

The IF… ELSE in MySQL executes code when a condition is TRUE, or a different code if the condition is FALSE. We have already seen what Custom Functions in MySQL are, now we will see how we can use the IF… ELSE inside our custom function.

The syntax for IF… ELSE:

IF condition THEN
      BEGIN
            /* … logic if the condition is true */
      END;
ELSE
      BEGIN
            /* … logic if the condition is false */
      END;
END IF;

We will see one example of a function to check if the number is Even or Odd. We will accept one number as a function input and will check if the number is even or odd using IF… ELSE and will return the output accordingly.

DELIMITER $$
CREATE FUNCTION `check_even`(
      in_value INTEGER
) RETURNS varchar(20) CHARSET utf8mb4
BEGIN
      IF in_value % 2 = 0 THEN
            BEGIN
                  return ‘Number is Even’;
            END;
      ELSE
            BEGIN
                  return ‘Number is Odd’;
            END;
      END IF;
END$$
DELIMITER ;

Once the above function is created we can run it by using the following query. We can change the input value for the function and check the result accordingly.

SELECT check_even(9) as result;

Multiple IF… ELSE Example.

We can also use multiple IF… ELSEIF… ELSE conditions like in the below example. We will write a function to perform arithmetic operations on the two numbers given in the input and operation mentioned as 3rd input parameter.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `arithmatic_operation`(
      in_value1 INTEGER,
      in_value2 INTEGER,
      in_operation VARCHAR(1)
) RETURNS varchar(100) CHARSET utf8mb4
BEGIN
      DECLARE result Varchar(100);

      IF ‘+’ = in_operation THEN
            BEGIN
                   SET result = concat(‘Sum of ‘ ,in_value1, ‘ and ‘, in_value2, ‘ is: ‘, (in_value1 + in_value2));
            END;
      ELSEIF ‘-‘ = in_operation THEN
            BEGIN
                  SET result = concat(‘Difference of ‘ ,in_value1, ‘ and ‘, in_value2, ‘ is: ‘, (in_value1 – in_value2));
            END;
      ELSEIF ‘*’ = in_operation THEN
            BEGIN
                  SET result = concat(‘Multiplication of ‘ ,in_value1, ‘ and ‘, in_value2, ‘ is: ‘, (in_value1 * in_value2));
            END;
      ELSEIF ‘/’ = in_operation THEN
            BEGIN
                  SET result = concat(‘Division of ‘ ,in_value1, ‘ and ‘, in_value2, ‘ is: ‘, (in_value1 / in_value2));
            END;
      ELSE
            BEGIN
                  SET result = ‘Invalid Operation’;
            END;
      END IF;
      return result;
END$$
DELIMITER ;