I would like to be able to retrieve result sets from a Stored Proc execution using PHP and sqlsrv and have both resultsets available for reading at the same time. Is this possible ?
Here a sample of what can be done, but without having 2 result sets available at once :
$connectionInfo = array("Database"=> 'myDatabase', "UID"=> 'myUser', "PWD" => "MyPassword", 'MultipleActiveResultSets' => true);
$conn = sqlsrv_connect("myServer", $connectionInfo);
$sql = "EXEC dbo.MyStoredProc;"; // Retrieves 2 result sets
$stmt = sqlsrv_query($conn, $sql);
$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
sqlsrv_next_result($stmt);
$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
// Now I cannot fetch from first result set because I called sqlsrv_next_result and cannot go back
With MARS enabled, I know I can have 2 active resultsets (but I need two statements and I need to make two queries to two different SP or the same SP and skip the first result, but that's not efficient) like this :
$connectionInfo = array("Database"=> 'myDatabase', "UID"=> 'myUser', "PWD"=> "MyPassword", 'MultipleActiveResultSets' => true);
$conn = sqlsrv_connect("myServer", $connectionInfo);
$sql = "EXEC dbo.MyOtherStoredProc;"; // Retrieve a single resultset
$stmt = sqlsrv_query($conn, $sql);
$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
$sql = "EXEC dbo.YetAnOtherStoredProc;"; // Retrieve a single resultset
$stmt2 = sqlsrv_query($conn, $sql);
$row2 = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC);
// Now I can fetch an other row from first statement on the same connection (which is not possible if MARS is not enabled)
$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
Since the results are pretty heavy, I just can't read the whole result at once and then call sqlsrv_next_result
that is why I would like to have both results readable at the same time.