IF-THEN Condition in MySQL Stored Procedure

In this tutorial, we are going to see how to use the IF-THEN condition in MySQL to execute a block of SQL code based on a specified condition. In this tutorial, we are going to see the IF statement not the IF() function.
 

Syntax:

IF expression THEN
   statements;
ELSE
   else-statements;
END IF;

 
Declarations must end with a semicolon.

To show the use of the IF statement in a MySQL stored procedure, we create the following stored procedure, based on the values from the table named ‘Stock’, as shown below:

> SELECT * FROM Stock

+----------+---------------+-----------+---------------+
|    ID    |    Product    |     qt    |   Category    |
+----------+---------------+-----------+---------------+
|     1    |   Pc          |     35    |   Computer    |
|     2    |   Keyboard    |     10    |   Computer    |
|     3    |   Mouse       |     12    |   Computer    |
|     4    |   Chocolate   |     114   |   Food        |
|     5    |   Mushroom    |     600   |   Food        |
+----------+---------------+-----------+---------------+

The following query will create a procedure named “checkProduct” which contains the IF-ELSE statements:

DELIMITER $
CREATE PROCEDURE checkProduct(IN Prod Varchar(20), OUT Category varchar(50))
BEGIN
   DECLARE p Varchar(20);
   SELECT product INTO p
   FROM stock WHERE product = Prod;
   IF p = 'Chocolate' THEN
   SET Category = 'This product belongs to Food category';
   ELSE
   SET Category = 'Product does not exist!';
   END IF;
END$
Delimiter ;

Now we can see the result below when we call this procedure:
 
CASE 1:

CALL checkProduct('Chocolate', @Category);
Select @Category;

Output:

+----------------------------------------+
| @Category                              |
+----------------------------------------+
| This product belongs to Food category  |
+----------------------------------------+

 
CASE 2:

CALL checkProduct('Oil', @Category);
Select @Category;

Output:

+----------------------------------------+
| @Category                              |
+----------------------------------------+
| Product does not exist!                |
+----------------------------------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More