What is the purpose of OUT in MySQL stored procedures?

Question

What is the purpose of OUT in MySQL stored procedures?

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

DELIMITER $$

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

This would give me all the results I want by running:

call new_routine(7);

So why would I want/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 call it like this

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

Which will give me just the name instead of everything from the rows returned?

I've tried Googling, but clearly haven't asked Google the right question to get a clear answer.

Answer 1

As quoted from MySQL doc on PROCEDURE

For each OUT or INOUT parameter, pass a user-defined variable in the CALL statement that invokes the procedure so that you can obtain its value when the procedure returns. If you are calling the procedure from within another stored procedure or function, you can also pass a routine 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 2

Yeah, you're right, with that second call you will now only get the name itself.

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

However the only "advantage" if you will is that you have a value in a variable instead of a result set which might seem more handy if you decide to use only that value further in your sql or whereever you want to work with it.

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