如何：處理錯誤和警告
根據預設，SQL Server Driver for PHP 會將警告視為錯誤；當呼叫會產生錯誤或警告的 sqlsrv 函數時，將會傳回 false。本主題示範如何關閉此預設行為，以及如何單獨處理警告和錯誤。
注意
將警告視為錯誤的預設行為有一些例外。對應至 SQLSTATE 值 01000、01001、01003 和 01S02 的警告絕不會被視為錯誤。
範例
下列程式碼範例會使用兩個使用者定義函數 DisplayErrors 和 DisplayWarnings 來處理錯誤和警告。此範例會示範如何藉由執行以下工作來分別處理警告和錯誤：
- 關閉將警告視為錯誤的預設行為。
- 建立一個預存程序，此程序會更新員工的休假時數，並將剩餘的休假時數當做輸出參數傳回。當員工的可用休假時數小於零時，此預存程序會印出一則警告。
- 更新幾位員工的休假時數 (其方式是針對每一個員工呼叫此預存程序)，並顯示對應至所發生之任何警告和錯誤的訊息。
- 顯示每位員工的剩餘休假時數。
請注意，在第一次呼叫 sqlsrv 函數 (sqlsrv_configure) 時，警告會被視為錯誤。因為警告會加入到錯誤集合中，所以您不必單獨檢查警告和錯誤。但是在後續的 sqlsrv 函數呼叫中，警告將不會被視為錯誤，所以您必須明確檢查警告和錯誤。
同時也請注意，範例程式碼會在每一次呼叫 sqlsrv 函數之後檢查是否有錯誤。這是建議的作法。
此範例假設 SQL Server 和 AdventureWorks 資料庫已經安裝在本機電腦上。當從命令列執行此範例時，所有輸出都會寫入主控台。當針對新安裝的 AdventureWorks 資料庫執行此範例時，它會產生三則警告和兩個錯誤。前兩個警告是標準警告，當您連接到資料庫時就會發出這些警告。第三個警告則是因為員工的可用休假時數更新為小於零的值。發生此錯誤是因為員工的可用休假時數更新為小於 -40 小時的值，這樣違反資料表的條件約束。
<?php
/* Turn off the default behavior of treating errors as warnings.
Note: Turning off the default behavior is done here for demonstration
purposes only. If setting the configuration fails, display errors and
exit the script. */
if( sqlsrv_configure("WarningsReturnAsErrors", 0) === false)
{
DisplayErrors();
die;
}
/* 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 the connection fails, display errors and exit the script. */
if( $conn === false )
{
DisplayErrors();
die;
}
/* Display any warnings. */
DisplayWarnings();
/* Drop the stored procedure if it already exists. */
$tsql1 = "IF OBJECT_ID('SubtractVacationHours', 'P') IS NOT NULL
DROP PROCEDURE SubtractVacationHours";
$stmt1 = sqlsrv_query($conn, $tsql1);
/* If the query fails, display errors and exit the script. */
if( $stmt1 === false)
{
DisplayErrors();
die;
}
/* Display any warnings. */
DisplayWarnings();
/* Free the statement resources. */
sqlsrv_free_stmt( $stmt1 );
/* Create the stored procedure. */
$tsql2 = "CREATE PROCEDURE SubtractVacationHours
@EmployeeID int,
@VacationHours smallint OUTPUT
AS
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - @VacationHours
WHERE EmployeeID = @EmployeeID;
SET @VacationHours = (SELECT VacationHours
FROM HumanResources.Employee
WHERE EmployeeID = @EmployeeID);
IF @VacationHours < 0
BEGIN
PRINT 'WARNING: Vacation hours are now less than zero.'
END;";
$stmt2 = sqlsrv_query( $conn, $tsql2 );
/* If the query fails, display errors and exit the script. */
if( $stmt2 === false)
{
DisplayErrors();
die;
}
/* Display any warnings. */
DisplayWarnings();
/* Free the statement resources. */
sqlsrv_free_stmt( $stmt2 );
/* Set up the array that maps employee ID to used vacation hours. */
$emp_hrs = array (7=>4, 8=>5, 9=>8, 11=>50);
/* Initialize variables that will be used as parameters. */
$employeeId = 0;
$vacationHrs = 0;
/* Set up the parameter array. */
$params = array(
array(&$employeeId, SQLSRV_PARAM_IN),
array(&$vacationHrs, SQLSRV_PARAM_INOUT)
);
/* Define and prepare the query to substract used vacation hours. */
$tsql3 = "{call SubtractVacationHours(?, ?)}";
$stmt3 = sqlsrv_prepare($conn, $tsql3, $params);
/* If the statement preparation fails, display errors and exit the script. */
if( $stmt3 === false)
{
DisplayErrors();
die;
}
/* Display any warnings. */
DisplayWarnings();
/* Loop through the employee=>vacation hours array. Update parameter
values before statement execution. */
foreach(array_keys($emp_hrs) as $employeeId)
{
$vacationHrs = $emp_hrs[$employeeId];
/* Execute the query. If it fails, display the errors. */
if( sqlsrv_execute($stmt3) === false)
{
DisplayErrors();
die;
}
/* Display any warnings. */
DisplayWarnings();
/*Move to the next result returned by the stored procedure. */
if( sqlsrv_next_result($stmt3) === false)
{
DisplayErrors();
die;
}
/* Display any warnings. */
DisplayWarnings();
/* Display updated vacation hours. */
echo "EmployeeID $employeeId has $vacationHrs ";
echo "remaining vacation hours.\n";
}
/* Free the statement and connection resources. */
sqlsrv_free_stmt( $stmt3 );
sqlsrv_close( $conn );
/* ------------- Error Handling Functions --------------*/
function DisplayErrors()
{
$errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
foreach( $errors as $error )
{
echo "Error: ".$error['message']."\n";
}
}
function DisplayWarnings()
{
$warnings = sqlsrv_errors(SQLSRV_ERR_WARNINGS);
if(!is_null($warnings))
{
foreach( $warnings as $warning )
{
echo "Warning: ".$warning['message']."\n";
}
}
}
?>