I had to use a MySQL stored procedure written by another team, so here I don’t question the design of the procedure in any way.

My task is to call the procedure and get the OUT parameters after the execution has completed. The call is made with PHP PDO.

Below is a sample stored procedure:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

DELIMITER $$

DROP PROCEDURE IF EXISTS `mydb`.`proc_name` $$
CREATE PROCEDURE `mydb`.`proc_name`(
    IN i_myInput INT,
    OUT o_statusCode INT,
    OUT o_statusMsg VARCHAR(255))
BEGIN
    IF i_myInput IS NULL THEN
        SET o_statusCode = 1;
        SET o_statusMsg  = 'Error: input parameter is NULL';
    ElSE
        SET o_statusCode = 0;
        SET o_statusMsg  = 'OK';
    END IF;

    SELECT NULL as `result`;
END $$

DELIMITER ;

PHP code to get the OUT parameters o_code and o_message

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# create PDO DB object
$mydb = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass");

$input = 5;
$proc = $mydb->prepare("CALL proc_name($input, @o_code, @o_message)");
$proc->execute();
$proc->closeCursor(); # !!! without that line, OUT parameters cannot be fetched

$output = $mydb->query("select @o_code, @o_message")->fetch(PDO::FETCH_ASSOC);
var_dump($output); // array('@o_code'=>'0', 'o_message'=>'OK')