What is the purpose of OUT in MySQL stored procedures?

Question

What is the purpose of OUT MySQL stored procedures?

If I have a simple stored procedure that looks like this:

 DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `new_routine`( IN iID int ) BEGIN select * from table1 where id = iID; END 

This will give me all the results I want to run:

 call new_routine(7); 

So why do I need to use OUT ?

 DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `new_routine`( IN iID int, OUT vName varchar(100) ) BEGIN select name into vName from table1 where id = iID; END 

and name it as follows

 call new_routine(7, @name); select @name; 

What will give me only the name and not all of the returned rows?

I tried Google, but obviously did not ask Google the right question to get a clear answer.

Answer 1

Yes, you are right, with this second call you will now receive only a name.

Out-Parameters for many people are usually considered bad practice, but they can be convenient if you want to get a value that you can work with after a call (which could also be calculated by a function). And in most cases, there is a better way to achieve what you want without using out-parameters.

However, the only “advantage” if you want is that you have a value in the variable instead of a result set, which may seem more convenient if you decide to use only this value further in your sql or if you want to work with it.

Therefore, in most cases, you should not use out-parameters, use functions instead. If you have procedures that return result sets AND out-parameters try to break them into smaller functions / procedures in order to avoid out-parameters, because it's just not nice to read and maintain;)

Answer 2

As stated in the MySQL doc document on PROCEDURE

For each OUT or INOUT parameter, pass a custom variable to CALL that calls the procedure so that you can get its value when the procedure returns. If you call a procedure from another stored procedure or function, you can also pass a regular parameter or local routine variable as an IN or INOUT parameter.

---

And later, an example:

 mysql> CREATE PROCEDURE simpleproc (OUT param1 INT) -> BEGIN -> SELECT COUNT(*) INTO param1 FROM t; -> END// Query OK, 0 rows affected (0.00 sec) 

---

 mysql> delimiter ; 

---

 mysql> CALL simpleproc(@a); Query OK, 0 rows affected (0.00 sec) 

---

 mysql> SELECT @a; +------+ | @a | +------+ | 3 | +------+ 1 row in set (0.00 sec) 

Answer 3

Some documentation: http://dev.mysql.com/doc/refman/5.0/en/call.html , maybe this will help, quote:

To return a value from a procedure using the OUT or INOUT parameter, pass the parameter using a custom variable, and then check the value of the variable after the procedure returns. (If you call a procedure from another stored procedure or function, you can also pass a regular parameter or local routine variable as IN or INOUT.) For the INOUT parameter, initialize its value before passing it to the procedure. The following procedure has an OUT parameter, which the procedure sets to the current version of the server, and an INOUT value, which the procedure increases by one from the current value:

 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; 

Answer 4

The use of the OUT keyword is indicated in the following article.

13.2.1. Syntax CALL

CALL can pass values ​​back to its caller using parameters that are declared as OUT or INOUT.