如何检测空结果集

本主题演示了如何在使用 SQL Server Driver for PHP 时确定某个结果集是否为空。

在 SQL Server Driver for PHP 的 1.0 版中，关于确定某个语句的结果集是否为空的建议做法是在语句中调用 sqlsrv_fetch、sqlsrv_fetch_array 或 sqlsrv_fetch_object，然后查看返回的值。如果活动结果集中没有结果，则该函数调用将返回 null。请注意，如果您在不返回结果集的语句（而非返回空结果集的语句）上调用这些函数之一，则它将返回 false。

在您使用 sqlsrv_fetch、sqlsrv_fetch_array 或 sqlsrv_fetch_object 时，必须先包括用于处理每一行数据的代码，然后才能继续调用任何这些函数。在继续调用任何这些函数之一后，已提取的每一行将不再可用。

本主题提供了两个示例。第一个示例检查多次执行简单查询时的空结果集。第二个示例检查由存储过程返回的空结果集。

示例

下面的示例演示了如何使用 sqlsrv_fetch_array 确定某个结果集是否为空。该示例多次执行一个查询，每次使用不同的参数值。请注意，第二次执行查询（使用“X”作为参数值）时返回了一个空结果集。该示例使用函数 (ProcessRow) 来处理包含结果的结果集的每一行。如果结果集为空，则会显示以下消息：“结果集为空。”

此示例假定本地计算机上已安装了 SQL Server 和 AdventureWorks 数据库。从命令行运行此示例时，所有的输出都将写入控制台。

<?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";
}
?>

为了使该示例重点检查空结果集，并没有对所有函数调用进行错误检查。有关错误处理的信息，请参阅处理错误和警告。

下一个示例演示了如何使用 sqlsrv_fetch 确定某个结果集是否为空。该示例多次执行一个存储过程，每次使用不同的参数值。该存储过程删除了某个销售订单中的明细项目 (SalesOrderID)，更新了相应产品 ID 的库存帐目，然后返回此销售订单的剩余明细项目。请注意，第二次执行查询（使用“121293”作为参数值）对应的是只有一个明细项目的订单。该存储过程删除了此明细项目，但是当它执行查询返回剩余的明细项目时，结果集为空。该示例使用函数 (ProcessRow) 来处理包含结果的结果集的每一行。如果结果集为空，则会显示以下消息：“结果集为空。”

此示例假定本地计算机上已安装了 SQL Server 和 AdventureWorks 数据库。从命令行运行此示例时，所有的输出都将写入控制台。

<?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";
}
?>

为了使该示例重点检查空结果集，并没有对所有函数调用进行错误检查。有关错误处理的信息，请参阅处理错误和警告。

另请参见

概念

比较数据检索函数
关于文档中的代码示例

其他资源

检索数据
更新数据 (SQL Server Driver for PHP)
API 参考 (SQL Server Driver for PHP)