Call stored procedure with multiple result sets with sqlsrv - no error receiving

Question

If I try to call a stored procedure with multiple result sets in PHP with PHP Driver for SQL Server (sqlsrv extension), I get result sets until an error occurs but I receive no error. Here is a short example:

Stored procedure:

CREATE PROCEDURE dbo.pr_testmultipleresult
AS
    BEGIN
        SET NOCOUNT ON;

        SELECT 1 AS result1;

        SELECT 2 AS result2;

        RAISERROR(
                     'This is an error' -- Message
                    ,16                 -- Severity,  
                    ,1                  -- State
                 );

        SELECT 3 AS result3;
    END;
GO

PHP code:

<?php
        $serverName = "localhost\SQL2012TEST";
        $connectionInfo = array("Database" => "TEST", "UID" => "", "PWD" => "");
        $conn = sqlsrv_connect($serverName, $connectionInfo);
        if ($conn === false) {
            die(print_r(sqlsrv_errors(), true));
        }
        $query = sqlsrv_query($conn, '{call dbo.pr_testmultipleresult}');
        if ($query === false) {
            die(print_r(sqlsrv_errors(), true));
        }
        do {

            while ($row = sqlsrv_fetch_array($query)) {
                // Loop through each result set and add to result array
                $result[] = $row;
            }
         } while (sqlsrv_next_result($query));

         echo "<pre>";
         \var_dump($result);
         echo "</pre>";

Result:

array(2) {
  [0]=>
  array(2) {
    [0]=>
    int(1)
    ["result1"]=>
    int(1)
  }
  [1]=>
  array(2) {
    [0]=>
    int(2)
    ["result2"]=>
    int(2)
  }
}

If the raiserror is called before the first select statement, I get an error in sqlsrv. But if there is only one select before the raiserror, I don't get it in sqlsrv.

Is there a configuration I have to add to get the errors?

Answer 1

Explanation:

When the stored procedure returns multiple result sets, you need to check for errors the result from sqlsrv_query() call and the result from each sqlsrv_next_result() call:

<?php

...

# T-SQL
$result = array();
$rc = 0;
$params = array(
    array(&$rc, SQLSRV_PARAM_OUT)
);

$query = sqlsrv_query($conn, 'EXEC ? = dbo.pr_testmultipleresult', $params);
if ($query === false) {
    echo "Error executing stored procedure.".print_r(sqlsrv_errors(), true)."<br>";
} else {
    while ($row = sqlsrv_fetch_array($query)) {
        $result[] = $row;
    }

    if (sqlsrv_next_result($query) === false) {
        echo "Error fetching a result set.".print_r(sqlsrv_errors(), true)."<br>";
    } else {
        while ($row = sqlsrv_fetch_array($query)) {
            $result[] = $row;
        }
    }   

    if (sqlsrv_next_result($query) === false) {
        echo "Error fetching a result set.".print_r(sqlsrv_errors(), true)."<br>";
    } else {
        while ($row = sqlsrv_fetch_array($query)) {
            $result[] = $row;
        }
    }   

    echo "<pre>";
    var_dump($result);
    echo "</pre>";  

    sqlsrv_free_stmt($query);
}

# End
sqlsrv_close($conn);
?>

Result:

Error fetching a result set.Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 50000 [code] => 50000 [2] => [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]This is an error [message] => [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]This is an error ) )
array(2) {
  [0]=>
  array(2) {
    [0]=>
    int(1)
    ["result1"]=>
    int(1)
  }
  [1]=>
  array(2) {
    [0]=>
    int(2)
    ["result2"]=>
    int(2)
  }
}

As a side note, one available option to configure error and warning handling is sqlsrv_configure() function, but I don't think it is useful in this specific case.

An optional solution:

I suggest to use the following approach, which is a possible solution to your problem (at least I'm able to execute a procedure, get all possible result sets and get the result status code).

You need to change the stored procedure and use RETURN to exit unconditionally from the procedure and to return an appropriate status. You also need to change the statement in the PHP script and use EXEC ? = dbo.pr_testmultipleresult statement with an output parameter to check the status, returned from the procedure. Note, that sqlsrv_query() returns the result from the statement execution, not the result from the stored procedure execution.

Stored procedure:

ALTER PROCEDURE dbo.pr_testmultipleresult
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @return int
    SET @return = 0

    SELECT 1 AS result1;

    BEGIN TRY  
        SELECT 1 / 0 AS Result2
    END TRY  
    BEGIN CATCH  
        SET @return = -1
    END CATCH 

    SELECT 3 AS result3;

    RETURN @return
END;

PHP:

<?php
$server = 'server\instance';
$database = 'database';
$uid = 'username';
$pwd = 'password';

# Connection
$cinfo = array("Database" => $database, "UID" => $uid, "PWD" => $pwd);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false) {
    die(print_r(sqlsrv_errors(), true));
}

# T-SQL
$result = array();
$rc = 0;
$params = array(
    array(&$rc, SQLSRV_PARAM_OUT)
);
$query = sqlsrv_query($conn, 'EXEC ? = dbo.pr_testmultipleresult', $params);
if ($query === false) {
    die(print_r(sqlsrv_errors(), true));
}
do {
    while ($row = sqlsrv_fetch_array($query)) {
        // Loop through each result set and add to result array
        $result[] = $row;
    }
} while (sqlsrv_next_result($query));

# Check result
if ($rc < 0) {
    echo "Result from stored procedure: ERROR";
} else {
    echo "Result from stored procedure: OK";
}   
echo "<br>"."Results:"."<br>";
echo print_r($result, true);

# End
sqlsrv_free_stmt($query);
sqlsrv_close($conn);
?>