Stored Procedures seem to be a rare bird in web development. But one thing is for sure, when you need them, its likely critical. With the last few versions of PHP we have some new arrivals: Sqlsrv data base extension for SQL Server, and PDO. While they don’t still have the new car smell, they do pose a bit of a learning curve.

The old way to get stored procedure results were pretty basic, call a query, get a single result set back. Now things are different. Stored procedures are powerful, and can do many queries and operations. With these new extensions we’re able to obtain these multiple result sets. Not only that, they can also have output parameters, meaning variables passed in can be updated within the procedure and updated back to php in our case. I’m going to show a quick example of how to get a stored procedure’s returned record sets and output parameters with the new php pdo driver with sqlsrv extension. Lets start with a look at the example stored procedure:

Text
CREATE PROCEDURE [dbo].[spGetSomethingById] 
    @Id int,
    @myemail varchar(50) output
AS
BEGIN
    insert into myTable (developerName, email, activated, about) values ('the name', 'asdf@fdsa.com', 1, 'this is about')
    
    select @myemail=rtrim(email) from myTable where id=1

    select id, developerName, email, activated, about from myTable
    
    select id, developerName, email, activated, about from myTable where id=@Id

    return 5
END

As you can see there is an insert and two select statements. These newer methods allow us to access the results of each query run, resulting in 3 result sets in this case. Now lets look at the php to call this:

Text
$conn = new PDO("sqlsrv:Server=127.0.0.1;Database=Testing","username","password");

$sql = "{:retval = CALL spGetSomethingById (@Id=:userID,@myemail=:userEmail)}";

$stmt = $conn->prepare($sql);

$retval = null;
$userID = 2;
$userEmail = "";

$stmt->bindParam('retval', $retval, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4);
$stmt->bindParam('userID', $userID, PDO::PARAM_INT);
$stmt->bindParam('userEmail', $userEmail, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 50);

$stmt->execute();

$results = array();
do {
    $results []= $stmt->fetchAll();
} while ($stmt->nextRowset());


echo '<pre>';
print_r($retval);echo "\n"; // the return value: 5
print_r($userEmail);echo "\n"; // email for record id=1
print_r($results);echo "\n"; // all record sets
echo '</pre>';

$stmt->closeCursor();
unset($stmt);

First off you will notice we call the stored procedure with a funny looking sql string.

Text
"{:retval = CALL spGetSomethingById (@Id=:userID,@myemail=:userEmail)}"

This essentially allows us to bind a variable to the “return” value. But don’t confuse the “return” value with the record sets. The record sets are generated from each query within the procedure. That being said once we execute the procedure we will then have an array of recordsets. We use a do while loop to grab each record set and append it cleanly to an array.

Text
$results = array();
do {
    $results []= $stmt->fetchAll();
} while ($stmt->nextRowset());

Now, here is another critical part. Until all record sets are traversed through, any output variables will not be cleanly available. In other words you should not use output variables until after our do while. (Place the print statements for the output variables above the loop to see why). So what does this print out exactly?

Text
5
bla@bla.com
Array
(
    [0] => Array
        (
        )
    [1] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [0] => 1
                    [developerName] => bla
                    [1] => bla
                    [email] => bla@bla.com
                    [2] => bla@bla.com
                    [activated] => 1
                    [3] => 1
                    [about] => bla
                    [4] => bla
                )
            [1] => Array
                (
                    [id] => 2
                    [0] => 2
                    [developerName] => someone
                    [1] => someone
                    [email] => else@else.com
                    [2] => else@else.com
                    [activated] => 0
                    [3] => 0
                    [about] => something
                    [4] => something
                )
            [2] => Array
                (
                    [id] => 4
                    [0] => 4
                    [developerName] => developer name
                    [1] => developer name
                    [email] => asdf@fdsa.com
                    [2] => asdf@fdsa.com
                    [activated] => 1
                    [3] => 1
                    [about] => this is about
                    [4] => this is about
                )
            [3 ... n]....
        )
    [2] => Array
        (
            [0] => Array
                (
                    [id] => 2
                    [0] => 2
                    [developerName] => someone
                    [1] => someone
                    [email] => else@else.com
                    [2] => else@else.com
                    [activated] => 0
                    [3] => 0
                    [about] => something
                    [4] => something
                )
        )
)

Now you can visually see the output. For backwards compatibility you may really be looking for the last record set, in this case $results[2] would be the last select in the stored procedure. You also may notice the empty result set at position 0. This is the insert query (not the first select query as it is simply assigning a value). To suppress these you should use “SET NOCOUNT ON” in your stored procedure. At that point insert, update and deletes will not return these empty sets.

With this approach we have individual results of 3 queries, and two output variables. Now we see the importance and power of stored procedures!