Export (0) Print
Expand All

How to: Detect Empty Result Sets

This topic demonstrates how to determine if a result set is empty when using the SQL Server Driver for PHP.

Cc761421.note(en-US,SQL.90).gifNote:
If you are using version 1.1 of the SQL Server Driver for PHP, you can use sqlsrv_has_rows to see if a result set has rows.

In version 1.0 of the SQL Server Driver for PHP, the recommended practice for determining if the result set for a statement is empty is to call sqlsrv_fetch, sqlsrv_fetch_array, or sqlsrv_fetch_object on the statement and examine the value returned. If the active result set does not contain results, the function call will return null. Note that if you call one of these functions on a statement that does not return a result set (as opposed to a statement that returns an empty result set), it will return false.

When you use sqlsrv_fetch, sqlsrv_fetch_array, or sqlsrv_fetch_object, you must include code to process each row of data before making the next call to any of these functions. Each row that has been fetched no longer be available after the next call to one of these functions.

This topic provides two examples. The first example checks for empty result sets when a simple query is executed multiple times. The second example checks for empty result sets returned by a stored procedure.

The following example demonstrates how to use sqlsrv_fetch_array to determine if a result set is empty. The example executes a query multiple times, each time with a different parameter value. Note that the second execution of the query (using "X" as the parameter value) returns an empty result set. The example uses a function (ProcessRow) to process each row of a result set that contains results. If the result set is empty, the following message is printed: "The result set is empty."

The example assumes that SQL Server and the AdventureWorks database are installed on the local computer. 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");
if( !($conn = sqlsrv_connect( $serverName, $connectionInfo)))
{
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Define the Tranasact-SQL query. */
$tsql = "SELECT EmailAddress FROM Person.Contact where LastName = ?";

/* Set up an array of parameter arrays. */
$param_arrays = array(
                      array("Jacobson"), 
                      array("X"), 
                      array("Akers")
                     );

/* Execute the query for each parameter array. */
foreach( $param_arrays as $params )
{
     if( !($stmt = sqlsrv_query($conn, $tsql, $params)))
     {
          echo "Error in statement execution.\n";
          die( print_r( sqlsrv_errors(), true));
     }

     echo "Results for LastName = $params[0]:\n";

     /* Determine if there are results.  If there are results, display
        them. If there are no results, display an appropriate message.
        If an error occured, display the error message.*/
     $row = sqlsrv_fetch_array( $stmt );
     if( $row )
     {
          /* Process each row of the result set and retrieve the next
             row.*/
          do
          {
               ProcessRow( $row ); 
               $row = sqlsrv_fetch_array( $stmt );
          } while( $row );
     }
     elseif( is_null($row))
     {
          echo "The result set is empty.\n";
     }
     elseif( $row === false )
     {
          die(print_r( sqlsrv_errors(), true ));
     }
     echo "----------------\n";
}

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

/* Function for processing a row of data. */
function ProcessRow( $row )
{
     echo $row[0]."\n";
}
?>

For the purposes of keeping this example focused on checking for empty results, not all function calls are checked for errors. For information about handling errors, see Handling Errors and Warnings.

The next example demonstrates how to use sqlsrv_fetch to determine if a result set is empty. The example executes a stored procedure multiple times, each time with a different parameter value. The stored procedure deletes a line item (SalesOrderID) in a sales order, updates the inventory entry for the corresponding product ID, then returns the remaining line items for the sales order. Note that the second execution of the query (using "121293" as the parameter value) corresponds to an order with only one line item. The stored procedure deletes this line item, but when it executes the query to return the remaining line items, the result set is empty. The example uses a function (ProcessRow) to process each row of a result set that contains results. If the result set is empty, the message is printed: "The result set is empty."

The example assumes that SQL Server and the AdventureWorks database are installed on the local computer. 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));
}

/* Drop the stored procedure if it already exists. */
$tsql_dropSP = "IF OBJECT_ID('UpdateInventory', 'P') IS NOT NULL 
                DROP PROCEDURE UpdateInventory";
$stmt1 = sqlsrv_query( $conn, $tsql_dropSP);
if( $stmt1 === false )
{
     echo "Error in executing statement 1.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Create the stored procedure. */
$tsql_createSP = "CREATE PROCEDURE UpdateInventory
                       @SalesOrderDetailID int
                  AS
                       BEGIN
                        --Get the SalesOrderID, ProductID, and Quantity
                        DECLARE @SalesOrderID int;
                        DECLARE @ProductID int;
                        DECLARE @OrderQty int;
                        SELECT @SalesOrderID = SalesOrderID,
                                   @ProductID = ProductID,
                                   @OrderQty = OrderQty
                        FROM Sales.SalesOrderDetail
                        WHERE SalesOrderDetailID = @SalesOrderDetailID;

                        --Update Inventory
                        UPDATE Production.ProductInventory 
                        SET Quantity = Quantity + @OrderQty 
                        WHERE ProductID = @ProductID;

                        --Delete the OrderDetail
                        DELETE FROM Sales.SalesOrderDetail
                        WHERE SalesOrderDetailID = @SalesOrderDetailID;

                        --Get remaining products for SalesOrderID
                        SELECT SalesOrderID,
                        SalesOrderDetailID,
                        ProductID,
                        OrderQty
                        FROM Sales.SalesOrderDetail
                        WHERE SalesOrderID = @SalesOrderID;
                    END";
$stmt2 = sqlsrv_query( $conn, $tsql_createSP);
if( $stmt2 === false)
{
     echo "Error in executing statement 2.\n";
     die( print_r( sqlsrv_errors(), true));
}
/*-------- The next few steps call the stored procedure. --------*/

/* Define the Transact-SQL query. Use question marks (?) in place of 
the parameters to be passed to the stored procedure */
$tsql_callSP = "{call UpdateInventory(?)}";

/* Define an array of parameter arrays. */
$param_arrays = array( 
                      array(16434),
                      array(121293)
                     );

/* Execute the query for each parameter array. */
foreach($param_arrays as $params)
{
     $stmt = sqlsrv_query( $conn, $tsql_callSP, $params);
     if( $stmt === false)
     {
          echo "Error in executing statement.\n";
          die( print_r( sqlsrv_errors(), true));
     }
     /* The first SELECT statement in the stored procedure only sets 
          the parameter values. It does not return a result set.*/
     /* Display the rows affected by the UPDATE statement in the stored 
        procedure and move to the next result. */
     echo "Rows updated: ".sqlsrv_rows_affected( $stmt )."\n";
     sqlsrv_next_result($stmt);

     /* Display the rows affected by the DELETE statement in the stored 
        procedure and move to the next result. */
     echo "Rows deleted: ".sqlsrv_rows_affected( $stmt )."\n";
     sqlsrv_next_result( $stmt );

     /* Determine if there are results returned by the SELECT statement
        of the stored procedure.  If there are results, display them.
        If there are no results, display an appropriate message. */
     $row = sqlsrv_fetch( $stmt );
     if( $row )
     {
          do
          {
               ProcessRow( $stmt );
               $row = sqlsrv_fetch( $stmt );
          }while( $row );
     }
     elseif( is_null( $row ))
     {
          echo "The result set is empty.\n";
     }
     elseif( $row === false )
     {
        die( print_r( sqlsrv_errors(), true));
     }
     echo "-------------------\n";
}

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

function ProcessRow( $stmt )
{
     echo "SalesOrderID: ".sqlsrv_get_field($stmt, 0)."\n";
     echo "SalesOrderDetailID: ".sqlsrv_get_field($stmt, 1)."\n";
     echo "ProductID: ".sqlsrv_get_field($stmt, 2)."\n";
     echo "OrderQty: ".sqlsrv_get_field($stmt, 3)."\n\n";
}
?>

For the purposes of keeping this example focused on checking for empty results, not all function calls are checked for errors. For information about handling errors, see Handling Errors and Warnings.

Community Additions

Show:
© 2014 Microsoft