MySQL Cookbook, 2nd Edition

Using a Stored Procedure to “Return” Multiple Values

Problem

You want to perform an operation that produces two or more values, but a stored function can return only a single value.

Solution

Use a stored procedure that has OUT or INOUT parameters, and pass user-defined variables for those parameters when you invoke the procedure. A procedure does not “return” a value the way a function does, but it can assign values to those parameters, which will be the values of the variables when the procedure returns.

Discussion

Unlike stored function parameters, which are input values only, a stored procedure parameter can be any of three types:

• An IN parameter is for input only. This is the default parameter type if you specify no type.

• An INOUT parameter is used to pass a value in, and it can also be used to pass a value back out.

• An OUT parameter is used to pass a value out.

This means that if you need to produce multiple values from an operation, you can use INOUT or OUT parameters. The following example illustrates this, using an IN parameter for input, and passing back three values via OUT parameters.

Creating Compound-Statement Objects showed an avg_mail_size() function that returns the average mail message size for a given sender. The function returns a single value. If you want additional information, such as the number of messages and total message size, a function will not work. You could write three separate functions, but it’s also possible to use a single procedure that retrieves multiple ...