sqlsrv_next_result
讓指定之陳述式的下一個結果 (結果集、資料列計數或輸出參數) 成為使用中狀態。
注意
批次查詢或預存程序傳回的第一個 (或唯一) 結果為使用中狀態,而不需要呼叫 sqlsrv_next_result。
語法
sqlsrv_next_result( resource $stmt )
參數
$stmt:讓下一個結果變成使用中狀態的已執行陳述式。
傳回值
如果已成功讓下一個結果變成使用中狀態,便會傳回布林值 true。如果讓下一個結果變成使用中狀態時發生錯誤,便會傳回 false。如果沒有其他結果可用,便會傳回 null。
範例
下列範例會建立及執行一個預存程序,此程序會將產品評論插入 Production.ProductReview 資料表中,然後針對指定的產品選取所有評論。在執行此預存程序之後,便會使用第一個結果 (受到此預存程序內 INSERT 查詢影響的資料列數),而不會呼叫 sqlsrv_next_result。下一個結果 (此預存程序內 SELECT 查詢所傳回的資料列) 是藉由呼叫 sqlsrv_next_result 而成為使用中,並使用 sqlsrv_fetch_array 加以耗用。
注意
建議的作法是使用標準語法呼叫預存程序。如需有關標準語法的詳細資訊,請參閱<呼叫預存程序>(英文)。
此範例假設 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('InsertProductReview', 'P') IS NOT NULL
DROP PROCEDURE InsertProductReview";
$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 InsertProductReview
@ProductID int,
@ReviewerName nvarchar(50),
@ReviewDate datetime,
@EmailAddress nvarchar(50),
@Rating int,
@Comments nvarchar(3850)
AS
BEGIN
INSERT INTO Production.ProductReview
(ProductID,
ReviewerName,
ReviewDate,
EmailAddress,
Rating,
Comments)
VALUES
(@ProductID,
@ReviewerName,
@ReviewDate,
@EmailAddress,
@Rating,
@Comments);
SELECT * FROM Production.ProductReview
WHERE ProductID = @ProductID;
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 InsertProductReview(?, ?, ?, ?, ?, ?)}";
/* Define the parameter array. */
$productID = 709;
$reviewerName = "Customer Name";
$reviewDate = "2008-02-12";
$emailAddress = "customer@email.com";
$rating = 3;
$comments = "[Insert comments here.]";
$params = array(
$productID,
$reviewerName,
$reviewDate,
$emailAddress,
$rating,
$comments
);
/* Execute the query. */
$stmt3 = sqlsrv_query( $conn, $tsql_callSP, $params);
if( $stmt3 === false)
{
echo "Error in executing statement 3.\n";
die( print_r( sqlsrv_errors(), true));
}
/* Consume the first result (rows affected by INSERT query in the
stored procedure) without calling sqlsrv_next_result. */
echo "Rows affectd: ".sqlsrv_rows_affected($stmt3)."-----\n";
/* Move to the next result and display results. */
$next_result = sqlsrv_next_result($stmt3);
if( $next_result )
{
echo "\nReview information for product ID ".$productID.".---\n";
while( $row = sqlsrv_fetch_array( $stmt3, SQLSRV_FETCH_ASSOC))
{
echo "ReviewerName: ".$row['ReviewerName']."\n";
echo "ReviewDate: ".date_format($row['ReviewDate'],
"M j, Y")."\n";
echo "EmailAddress: ".$row['EmailAddress']."\n";
echo "Rating: ".$row['Rating']."\n\n";
}
}
elseif( is_null($next_result))
{
echo "No more results.\n";
}
else
{
echo "Error in moving to next result.\n";
die(print_r(sqlsrv_errors(), true));
}
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt1 );
sqlsrv_free_stmt( $stmt2 );
sqlsrv_free_stmt( $stmt3 );
sqlsrv_close( $conn );
?>
當執行具有輸出參數的預存程序時,建議您最好先耗用所有其他結果,然後再存取輸出參數的值。如需詳細資訊,請參閱<如何:指定參數方向>。