MySQL Stored Procedures That Return Multiple Values

 Summary: in this tutorial, you will learn how to develop stored procedures that return multiple values.

MySQL stored function returns only one value. To develop stored programs that return multiple values, you need to use stored procedures with INOUT or OUT parameters.

If you are not familiar with INOUT or OUT parameters, please refer to the tutorial on stored procedure’s parameters for detailed information.

Stored procedures that return multiple values example

Let’s take a look at the orders table in the sample database:

The following creates a stored procedure that accepts the customer number and returns the counts of orders with different statuses (shipped, canceled, resolved, disputed) for that customer:

DELIMITER $$

CREATE PROCEDURE get_order_by_cust(
	IN cust_no INT,
	OUT shipped INT,
	OUT canceled INT,
	OUT resolved INT,
	OUT disputed INT)
BEGIN
		-- shipped
		SELECT
            count(*) INTO shipped
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Shipped';

		-- canceled
		SELECT
            count(*) INTO canceled
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Canceled';

		-- resolved
		SELECT
            count(*) INTO resolved
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Resolved';

		-- disputed
		SELECT
            count(*) INTO disputed
        FROM
            orders
        WHERE
            customerNumber = cust_no
                AND status = 'Disputed';

ENDCode language: SQL (Structured Query Language) (sql)

Inside the stored procedure, we use a SELECT statement with the COUNT function to retrieve the total number of orders corresponding to each order’s status and assign it to the respective parameter.

To use the get_order_by_cust stored procedure, you pass the customer number and four user-defined variables to get the out values.

After executing the stored procedure, you use the SELECT statement to output the variable values.

CALL get_order_by_cust(141,@shipped,@canceled,@resolved,@disputed);
SELECT @shipped,@canceled,@resolved,@disputed;Code language: SQL (Structured Query Language) (sql)

Calling stored procedures that return multiple values from PHP

The following code snippet shows you how to call the stored procedure that returns multiple values from PHP.

<?php
/**
 * Call stored procedure that return multiple values
 * @param $customerNumber
 */
function call_sp($customerNumber)
{
    try {
        $pdo = new PDO("mysql:host=localhost;dbname=classicmodels", 'root', '');

        // execute the stored procedure
        $sql = 'CALL get_order_by_cust(:no,@shipped,@canceled,@resolved,@disputed)';
        $stmt = $pdo->prepare($sql);

        $stmt->bindParam(':no', $customerNumber, PDO::PARAM_INT);
        $stmt->execute();
        $stmt->closeCursor();

        // execute the second query to get values from OUT parameter
        $r = $pdo->query("SELECT @shipped,@canceled,@resolved,@disputed")
                  ->fetch(PDO::FETCH_ASSOC);
        if ($r) {
            printf('Shipped: %d, Canceled: %d, Resolved: %d, Disputed: %d',
                $r['@shipped'],
                $r['@canceled'],
                $r['@resolved'],
                $r['@disputed']);
        }
    } catch (PDOException $pe) {
        die("Error occurred:" . $pe->getMessage());
    }
}

call_sp(141);Code language: PHP (php)

The user-defined variables, which are preceded by the @ sign, are associated with the database connection, therefore, they are available for access between the calls.

In this tutorial, we have shown you how to develop a stored procedure that returns multiple values and how to call it from PHP.