Implement Conditional MySQL Query in a Stored Procedure

For conditional MySQL query, you can use IF ELSE concept in stored procedure. Let us first create a table −

mysql> create table DemoTable1
   (
   Id int
   );
Query OK, 0 rows affected (0.62 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1 values(10);
Query OK, 1 row affected (0.19 sec)

mysql> insert into DemoTable1 values(20);
Query OK, 1 row affected (0.12 sec)

mysql> insert into DemoTable1 values(30);
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable1;

This will produce the following output −

+------+
| Id   |
+------+
| 10   |
| 20   |
| 30   |
+------+
3 rows in set (0.00 sec)

Following is the query to create second table.

mysql> create table DemoTable2
   (
   Id int
   );
Query OK, 0 rows affected (1.07 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2 values(40);
Query OK, 1 row affected (0.13 sec)

mysql> insert into DemoTable2 values(50);
Query OK, 1 row affected (0.17 sec)

mysql> insert into DemoTable2 values(60);
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable2;

This will produce the following output −

+------+
| Id   |
+------+
| 40   |
| 50   |
| 60   |
+------+
3 rows in set (0.00 sec)

Following is the query to create a stored procedure for conditional MySQL query.

mysql> DELIMITER //
   mysql> CREATE PROCEDURE Conditional_Query(id int)
   BEGIN
      IF id >=40 THEN
         Select * from DemoTable2;
     ELSE
        Select * from DemoTable1;
     END IF;
   END
   //
   Query OK, 0 rows affected (0.21 sec)
mysql> DELIMITER ;

Now you can call the stored procedure with the help of CALL command −

mysql> call Conditional_Query(100);

This will produce the following output −

+------+
| Id   |
+------+
| 40   |
| 50   |
| 60   |
+------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Call again and pass a different value −

mysql> call Conditional_Query(13);

This will produce the following output −

+------+
| Id   |
+------+
| 10   |
| 20   |
| 30   |
+------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)