Recipe 16.3. Using a Stored Procedure to "Return" Multiple ValuesProblemYou want to perform an operation that produces two or more values, but a stored function can return only a single value. SolutionUse 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. DiscussionUnlike stored function parameters, which are input values only, a stored procedure parameter can be any of three types:
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. Section 16.1 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 values about a given mail sender. The following procedure, mail_sender_stats( ), runs a query on the mail table to retrieve mail-sending statistics about a given username, which is the input value. The procedure determines how many messages that user sent, and the total and average size of the messages in bytes, which it returns through three OUT parameters: CREATE PROCEDURE mail_sender_stats(IN user VARCHAR(8), OUT messages INT, OUT total_size FLOAT, OUT avg_size FLOAT) BEGIN # Use IFNULL() to return 0 for SUM() and AVG() in case there are # no rows for the user (those functions return NULL in that case). SELECT COUNT(*), IFNULL(SUM(size),0), IFNULL(AVG(size),0) INTO messages, total_size, avg_size FROM mail WHERE srcuser = user; END; To use the procedure, pass a string containing the username, and three user-defined variables to receive the OUT values. After the procedure returns, check the variable values: mysql> CALL mail_sender_stats('barb',@messages,@total_size,@avg_size); mysql> SELECT @messages, @total_size, @avg_size; +-----------+-------------+-----------+ | @messages | @total_size | @avg_size | +-----------+-------------+-----------+ | 3 | 156696 | 52232 | +-----------+-------------+-----------+ |