[SOLVED] What is the purpose of OUT in MySQL stored procedures?

What is the purpose of OUT in MySQL stored procedures?

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:

```sql
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.

Solution

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 ;)