How to use IF in stored procedure and select in MySQL?

MySQLMySQLi Database

You can use IF in stored procedure and IF() in select statement as well.

IF() in select statement

IF() in select statement mysql> select if(0=0,'Hello MySQL','condition is wrong');

This will produce the following output −

+------------------------------------------------------+
| if('test'='test','Hello MySQL','condition is wrong') |
+------------------------------------------------------+
| Hello MySQL                                          |
+------------------------------------------------------+
1 row in set (0.00 sec)

The second case if your condition becomes wrong −

mysql> select if(1=0,'Hello MySQL','condition is wrong');

This will produce the following output −

+--------------------------------------------+
| if(1=0,'Hello MySQL','condition is wrong') |
+--------------------------------------------+
| condition is wrong                         |
+--------------------------------------------+
1 row in set (0.00 sec)

If in stored procedure

The query to create a stored procedure is as follows. Here, we have used IF to set conditions −

mysql> DELIMITER //
mysql> CREATE PROCEDURE if_demo(value int)
   BEGIN
      IF 1=value
         THEN SELECT "Hello MySQL";
      ELSE
         SELECT "Wrong Condition";
      END IF;
   END
   //
Query OK, 0 rows affected (0.20 sec)
mysql> DELIMITER ;

Now you can call the stored procedure using call command.

mysql> call if_demo(1);

This will produce the following output −

+-------------+
| Hello MySQL |
+-------------+
| Hello MySQL |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

If your condition becomes false −

mysql> call if_demo(0);

This will produce the following output −

+-----------------+
| Wrong Condition |
+-----------------+
| Wrong Condition |
+-----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
raja
Published on 30-Apr-2019 10:27:47
Advertisements