Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

How to: Work with Multiple Result Sets

The SQL Server Driver for PHP provides the sqlsrv_next_result function for making the next result (result set, row count, or output parameter) of a statement active. This topic demonstrates how to use sqlsrv_next_result to move from one result of a batch query to the next. For an example of retrieving an output parameter returned by a stored procedure, see the sqlsrv_next_result.

The following example executes a batch query that retrieves product review information for a specified product ID, inserts a review for the product, then again retrieves the product review information for the specified product ID. The newly inserted product review will be included in the final result set of the batch query. The example uses sqlsrv_next_result to move from one result of the batch query to the next.

Cc626309.note(en-US,SQL.90).gifNote:
The first (or only) result returned by a batch query or stored procedure is active without a call to sqlsrv_next_result.

The example uses the Purchasing.ProductReview table of the AdventureWorks database, and assumes that this database is installed on the server. All output is written to the console when the example is run from the command line.

<?php
/* Connect to the local server using Windows Authentication and
specify the AdventureWorks database as the database in use. */
$serverName = "(local)";
$connectionInfo = array( "Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Define the batch query. */
$tsql = "--Query 1
         SELECT ProductID, ReviewerName, Rating 
         FROM Production.ProductReview 
         WHERE ProductID=?;

         --Query 2
         INSERT INTO Production.ProductReview (ProductID, 
                                               ReviewerName, 
                                               ReviewDate, 
                                               EmailAddress, 
                                               Rating)
         VALUES (?, ?, ?, ?, ?);

         --Query 3
         SELECT ProductID, ReviewerName, Rating 
         FROM Production.ProductReview 
         WHERE ProductID=?;";

/* Assign parameter values and execute the query. */
$params = array(798, 
                798, 
                'CustomerName', 
                '2008-4-15', 
                'test@customer.com', 
                3, 
                798 );
$stmt = sqlsrv_query($conn, $tsql, $params);
if( $stmt === false )
{
     echo "Error in statement execution.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Retrieve and display the first result. */
echo "Query 1 result:\n";
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC ))
{
     print_r($row);
}

/* Move to the next result of the batch query. */
sqlsrv_next_result($stmt);

/* Display the result of the second query. */
echo "Query 2 result:\n";
echo "Rows Affected: ".sqlsrv_rows_affected($stmt)."\n";

/* Move to the next result of the batch query. */
sqlsrv_next_result($stmt);

/* Retrieve and display the third result. */
echo "Query 3 result:\n";
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC ))
{
     print_r($row);
}

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt );
sqlsrv_close( $conn );
?>

Community Additions

Show:
© 2015 Microsoft