How can I create MySQL stored procedure with INOUT parameter?

Following example will demonstrate MySQL stored procedure with INOUT parameter −

mysql> DELIMITER // ;
mysql> Create PROCEDURE counter(INOUT count INT, IN increment INT)
    -> BEGIN
    -> SET count = count + increment;
    -> END //
Query OK, 0 rows affected (0.03 sec)

Here, ‘count’ is the INOUT parameter, which can store and return values and ‘increment’ is the IN parameter, which accepts the values from user.

mysql> DELIMITER ;
mysql> SET @counter = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL counter(@Counter, 1);
Query OK, 0 rows affected (0.00 sec)

mysql> Select @Counter;
+----------+
| @Counter |
+----------+
| 1        |
+----------+
1 row in set (0.00 sec)

mysql> CALL counter(@Counter, 5);
Query OK, 0 rows affected (0.00 sec)

mysql> Select @Counter;
+----------+
| @Counter |
+----------+
| 6        |
+----------+
1 row in set (0.00 sec)

Nishtha Thakur

Published on 21-Feb-2018 07:57:58

• Related Questions & Answers
• How can I create MySQL stored procedure with IN parameter?
• How can I create MySQL stored procedure with OUT parameter?
• MySQL stored-procedure: out parameter?
• What is stored procedure and how can we create MySQL stored procedures?
• How can a MySQL stored procedure call another MySQL stored procedure inside it?
• How can I create a stored procedure to insert values in a MySQL table?
• How can I create a stored procedure to delete values from a MySQL table?
• How can I create a stored procedure to update values in a MySQL table?
• Create a stored procedure with delimiter in MySQL
• How can I create a MySQL stored procedure that returns multiple values from a MySQL table?
• How can we invoke MySQL stored procedure?
• How can we create MySQL stored procedure to calculate the factorial?
• Create variables in MySQL stored procedure with DECLARE keyword
• How can we alter a MySQL stored procedure?
• How can we drop a MySQL stored procedure?