Can we use “IF NOT IN” in a MySQL procedure?


Let us first see the syntax of IF NOT IN in MySQL −

if(yourVariableName  NOT IN (yourValue1,yourValue2,........N) ) then
   statement1
else
   statement2
endif    

Let us implement the above syntax to use IF NOT IN −

mysql> DELIMITER //
mysql> CREATE PROCEDURE IF_NOT_INDemo(IN value int)
   ->    BEGIN
   ->       if(value NOT IN  (10,20,30) ) then
   ->          select "Value Not Found";
   ->       else
   ->          select "Value Found";
   ->       end if;
   ->    END
   -> //
Query OK, 0 rows affected (0.25 sec)
mysql> DELIMITER ;

Now call the stored procedure using CALL command.

Case 1 − When a value is found −

mysql> call IF_NOT_INDemo(10);

Output

+-------------+
| Value Found |
+-------------+
| Value Found |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Case 2 - When a value isn’t found -

mysql> call IF_NOT_INDemo(100);

Output

 +-----------------+
| Value Not Found |
+-----------------+
| Value Not Found |
+-----------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.07 sec)
raja
Published on 11-Jul-2019 11:34:02
Advertisements