How to declare a variable inside a procedure in MySQL?

MySQLMySQLi Database

You can use the DECLARE command to declare a variable inside a MySQL procedure. Let us create a stored procedure in MySQL −

mysql> DELIMITER //
mysql> CREATE PROCEDURE DECLARE_VARIABLE_DEMO(IN value int)
   -> BEGIN
   -> DECLARE searchValue int;
   -> set searchValue=value;
   -> if searchValue=10 then
   ->    select searchValue+100;
   -> else
   ->    select searchValue;
   -> end if;
   -> END
   -> //
Query OK, 0 rows affected (0.17 sec)
mysql> DELIMITER ;

Above, we have declared a variable. Now, let us call the stored procedure using CALL command −

mysql> call DECLARE_VARIABLE_DEMO(10);

This will produce the following output −

+-----------------+
| searchValue+100 |
+-----------------+
|             110 |
+-----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
raja
Published on 12-Dec-2019 07:12:42
Advertisements