`INOUT` parameter for a MySQL procedure

Question

Could you please give me a simple example of INOUT in MySQL stored procedure?

Citrus

Answer 1

I think searching Google will give you tons of examples!!
One (taken from here)

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`get_users` $$
CREATE PROCEDURE `get_users`(
    IN firstName VARCHAR(100),
    OUT totalUsers INT
)
BEGIN
    SELECT COUNT(*) INTO totalUsers
    FROM users
    WHERE first_name = firstName;
    SELECT * FROM users
    WHERE first_name = firstName;
END $$
DELIMITER ;

Post says:

Notice there are two statements in the body of this stored procedure. The first select count(*) statement counts the total number of people who’s first name is equal to the in variable firstName. Once it’s gets the count, it sets the out variable totalUsers to that value.

The next statement is a simple select. This will select all fields for users who’s first name is equal to the in variable firstName and return the recordset. So by calling this stored procedure and passing in two parameters (first name, total), a recordset will be returned and an out variable will be set – that can then be queried.

EDITED:
As in MySQL website:

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END;

Boohoo

Is there a procedure something like CREATE PROCEDURE Capitalize(INOUT str VARCHAR(1024)) ? Can you see INOUT? Whats the difference between this INOUT and yours with IN and OUT combination? Could you please elaborate? – Citrus

@user1251490: IN means input only, OUT means output only, INOUT means both input and output. Look at my example taken from MySQL website. – Boohoo

@user1251490: did you find my post useful? – Boohoo

Answer 2

Example of INOUT usage in MySQL:

From the terminal run mysql -u root -p.

el@apollo:~$ mysql -u root -p
Enter password: 

Change to your database:

mysql> use yourdb;
Reading table information for completion of table and column names

Create a variable to feed into your stored procedure called msg.

mysql> select 'ricksays' into @msg;
Query OK, 1 row affected (0.00 sec)

Create the stored procedure:

mysql> delimiter //
mysql> create procedure foobar (inout msg varchar(100))
-> begin
-> set msg = concat(@msg, " never gonna let you down");
-> end//

Set the delimiter back:

mysql> delimiter ;

Invoke the stored procedure, pass in the variable.

mysql> call foobar(@msg);
Query OK, 0 rows affected (0.00 sec)

Ok, Now see if it worked:

mysql> select @msg;
+-----------------------------------+
| @msg                              |
+-----------------------------------+
| ricksays never gonna let you down |
+-----------------------------------+
1 row in set (0.00 sec)

Ok it worked, it joined the strings together. So you defined a variable msg, passed in that variable into stored procedure called foobar, and @msg was written to by foobar.

Which is the purpose of the INOUT parameter. You could use this to solve problems.

Recrystallize

Answer 3

If like me, the confusion was the difference between OUT and INOUT. Which was clarified with these points found in the MySQL Certification Study Guide

OUT - Any value the parameter has when it is passed is ignored by the procedure, and its initial value within the procedure is NULL

INOUT - The value passed by the caller is the parameter's initial value within the procedure

Underwood

Answer 4

An INOUT parameter:

• can get the value from the caller and return the value from a procedure to the caller with a user-defined session variable.

• cannot accept a raw value without using a user-defined session variable from the caller.

*My answer and my answer explains an IN and OUT parameters respectively and the doc explains an INOUT parameter.

For example, you create test table as shown below:

CREATE TABLE test (
  num int
);

Then, you insert the row whose num is 2 as shown below:

INSERT INTO test (num) VALUES (2);

Now, you can create addition(INOUT result INT) procedure which adds result to num and returns num value with result to the caller as shown below:

DELIMITER $$

CREATE PROCEDURE addition(INOUT result INT)
BEGIN
UPDATE test SET num = num + result;
SELECT num INTO result FROM test;
END$$

DELIMITER ;

Then, you can set 3 to the user-defined session variable e.g. @r, then call addition(@r) with CALL statement, then 3 is added to num and @r has num value 5 as shown below. *The initial value of a user-defined session variable is NULL:

mysql> SET @r = 3;
...
mysql> CALL addition(@r);
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|   5  |
+------+
...
mysql> SELECT @r;
+------+
| @r   |
+------+
|   5  |
+------+

Be careful, passing the raw value 3 to result parameter gets the error below because as I say above, an INOUT parameter cannot accept a raw value without using a user-defined session variable from the caller.

mysql> CALL addition(3);
ERROR 1414 (42000): OUT or INOUT argument 1 for routine apple.addition is not a variable or NEW pseudo-variable in BEFORE trigger

In addition, you can create addition() procedure with the user-defined session variable e.g. @r and without an INOUT parameter as shown below:

DELIMITER $$

CREATE PROCEDURE addition()
BEGIN                    -- ↓↓ Here
UPDATE test SET num = num + @r;
SELECT num INTO @r FROM test;
END$$        -- ↑↑ Here

DELIMITER ;

Then, you set @r with 3 and call addition(), then 3 is added to num and @r has num value 5 as shown below:

mysql> SET @r = 3;
...
mysql> CALL addition();
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|    5 |
+------+
...
mysql> SELECT @r;
+------+
| @r   |
+------+
|   5  |
+------+

Junker