How to elegantly handle multiple result sets of SQL Server with next_result()

gitbox 2025-05-06

When operating SQL Server in PHP (especially using SQLSRV extensions), we often encounter situations returned by stored procedures or batch query statements. To handle these result sets correctly, using sqlsrv_fetch_array() is not enough, we also need to use the sqlsrv_next_result() function with it. This article will explain how to use next_result() elegantly and safely to iterate through all returned result sets.

What is next_result() ?

In SQL Server, a query can return multiple result sets, for example:

 SELECT * FROM users;
SELECT * FROM orders;

When executing such a query, PHP must use sqlsrv_next_result() to iterate over the two result sets.

Basic usage method

Let's first look at a simple example:

 <?php
$serverName = "localhost";
$connectionOptions = array(
    "Database" => "MyDatabase",
    "Uid" => "myuser",
    "PWD" => "mypassword"
);

// Establish a connection
$conn = sqlsrv_connect($serverName, $connectionOptions);
if ($conn === false) {
    die(print_r(sqlsrv_errors(), true));
}

// Execute multiple result sets SQL Query
$sql = "SELECT * FROM users; SELECT * FROM orders;";
$stmt = sqlsrv_query($conn, $sql);

if ($stmt === false) {
    die(print_r(sqlsrv_errors(), true));
}

$resultIndex = 1;

do {
    echo "Handle the {$resultIndex} A result set：\n";
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        print_r($row);
    }
    $resultIndex++;
} while (sqlsrv_next_result($stmt));

// Free up resources
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>

Examples of usage scenarios: paging query and stored procedures

In some enterprise applications, commonly used stored procedures to implement paging, and this type of stored procedures often returns two result sets: one is a data list and the other is a total number of records:

 -- Assuming paging stored procedure
CREATE PROCEDURE GetPagedUsers
AS
BEGIN
    SELECT * FROM users ORDER BY id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
    SELECT COUNT(*) AS TotalCount FROM users;
END

When we process it in PHP, we can do this:

 $sql = "{CALL GetPagedUsers()}";
$stmt = sqlsrv_query($conn, $sql);

if ($stmt === false) {
    die(print_r(sqlsrv_errors(), true));
}

// 第一A result set：Data list
$users = [];
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    $users[] = $row;
}

// 移动到下一A result set
if (sqlsrv_next_result($stmt)) {
    // 第二A result set：Total records
    if ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        $totalCount = $row['TotalCount'];
    }
}

print_r([
    'users' => $users,
    'totalCount' => $totalCount,
]);

Things to note

Resource release : Be sure to call sqlsrv_free_stmt() at the end to release the statement resource.
The order of result sets should be clear : the order of multiple result sets is entirely determined by the order of SQL.
Prevent empty result sets : After calling sqlsrv_next_result(), you must check whether new data has been returned.

summary

By rationally using sqlsrv_next_result() , we can gracefully process multiple result sets returned by SQL Server, playing a huge role in business scenarios such as paging, statistics, and merge queries. Remember, when calling such interfaces in a production environment, you must do a good job of error handling and resource release to ensure the stable operation of the system.

To learn more about how SQL Server and PHP are combined, you can access the official documentation or our tutorial platform: https://gitbox.net/php-sqlserver .

next_result