How to call a MySQL stored procedure with output parameters

In this article we'll figure out how to call MySQL stored procedures from PHPRunner events. 

Here is our stored procedure that accepts two parameters, adds them up and returns the result in OUT parameter total.

DELIMITER $$
CREATE PROCEDURE mysum(IN par1 decimal(20,2),
                                 IN par2 decimal(20,2),
         OUT total decimal(20,2))
BEGIN
    SET total = par1 + par2;
END$$
DELIMITER ;

You can execute the following via phpMyAdmin or Navicat to make sure your stored procedure works:

call mysum(10.5,25,@total);
select @total as t;

It will return the value of 35.50. Now lets see how we can call this stored procedure from PHPRunner event like BeforeAdd. In the example we add up two fields and save the result in another field.

CustomQuery("call mysum(".$values["field1"].",".$values["field2"].",@total);");
$rs = CustomQuery("select @total;");
$data = db_fetch_array($rs);
$values["field3"]=$data["@total"];

First SQL query makes stored procedure call, sending values of field1 and field2 as parameters. Second SQL query reads the output variable.