The MySQL database provides you with an IF statement that you can use in stored procedures and functions.
The IF statement allows you to create conditional queries that will be executed only when a specified condition has been met.
For example, suppose you want to check if a TEXT value length is greater than 5 or not.
You can create a charLength() function with an IF statement as shown below:
DELIMITER //
CREATE FUNCTION charLength(str TEXT)
RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE result TEXT DEFAULT CONCAT(str, " length is 5 or less");
IF CHAR_LENGTH(str)> 5 THEN
SET result = CONCAT(str, " length is greater than 5 ");
END IF;
RETURN result;
END //
DELIMITER ;
Then, you can use the SELECT statement to call the function as follows:
SELECT charLength("Hello"),
charLength("Morning");
The returned output will be as shown below:
+---------------------------+-----------------------------------+
| charLength("Hello") | charLength("Morning") |
+---------------------------+-----------------------------------+
| Hello length is 5 or less | Morning length is greater than 5 |
+---------------------------+-----------------------------------+
1 row in set (0.00 sec)
The IF statement can be combined with the ELSE statement to provide an alternative query to execute when the IF statement condition is not met.
For example, suppose you want to compare two INT values and print a text that says x is larger than y or x is smaller than y.
You can create a function in MySQL and create a conditional IF ELSE statement as follows:
DELIMITER //
CREATE FUNCTION compare(x INT, y INT)
RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE result TEXT;
IF x > y THEN
SET result = CONCAT(x, " is larger than ", y);
ELSE
SET result = CONCAT(x, " is smaller than ", y);
END IF;
RETURN result;
END //
DELIMITER ;
Now you can call the function using the SELECT statement as follows:
SELECT compare(1, 2), compare(10, 5);
The result set will be as shown below:
+---------------------+---------------------+
| compare(1, 2) | compare(10, 5) |
+---------------------+---------------------+
| 1 is smaller than 2 | 10 is larger than 5 |
+---------------------+---------------------+
1 row in set (0.00 sec)
And that’s how you create an IF ELSE statement in MySQL functions.
When you have more than two conditions, you can also use multiple ELSEIF statements where you define each condition you want to check in a sequence.
Here’s another example function where you also need to check if the two INT values are equal:
DELIMITER //
CREATE FUNCTION compare(x INT, y INT)
RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE result TEXT;
IF x > y THEN
SET result = CONCAT(x, " is larger than ", y);
ELSEIF x < y THEN
SET result = CONCAT(x, " is smaller than ", y);
ELSE
SET result = CONCAT(x, " and ", y, " are equal");
END IF;
RETURN result;
END //
DELIMITER ;
Now the function will run the ELSE statement only when the value of x is neither larger or smaller than the value of y.
And that’s how you can use IF, ELSEIF, and ELSE statements in MySQL function and stored procedure.
