解决 sqlsrv_query 无法返回结果集的问题：最佳实践与参数化查询

// 拼接字符串时，日期时间格式可能不被SQL Server明确识别
$sql_q = "WHERE '" . $dateDebut->format('Y-m-d H:i:s.').'000' . "' <= TblOrder.FldOrdCre";

$dateDebut->format('Y-m-d\TH:i:s').'.000' // 注意 'T' 分隔符和毫秒部分

$sql_q = "SELECT ... WHERE '" . $dateDebut->format('Y-m-d H:i:s.').'000' . "' <= TblOrder.FldOrdCre AND TblOrder.FldOrdCre <= '" . $dateFin->format('Y-m-d H:i:s.').'000' . "'";
$query= sqlsrv_query($conn, $sql_q, array(), array( "Scrollable" => 'static' ));

$sql_q = "SELECT ... WHERE ? <= TblOrder.FldOrdCre AND TblOrder.FldOrdCre <= ?";
$params = array(
    $dateDebut->format('Y-m-d\TH:i:s').'.000',
    $dateFin->format('Y-m-d\TH:i:s').'.000'
);
$query = sqlsrv_query($conn, $sql_q, $params);

if (sqlsrv_num_rows($query) > 0) {
    // ...
}

if (sqlsrv_has_rows($query)) {
    // ...
}

<?php
// 假设 $dateDebut 和 $dateFin 已经是有效的 DateTime 对象
// 例如：
// $dateDebut = new DateTime('2023-01-01 00:00:00');
// $dateFin = new DateTime('2023-12-31 23:59:59');

$connInfo = array( "Database"=>"xxx", "UID"=>"xxx", "PWD"=>"xxx", "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect('SQL2008', $connInfo);

if ($conn) {
    // 构造 SQL 查询字符串，使用占位符 ? 进行参数化
    $sql_q = "
        SELECT 
            TblOrder.FldJobNb, 
            TblOrder.FldOrdCre As DateReception, 
            TblOrder.FldReclamerDate As DateDebutPORev, 
            TblOrder.FldPOReviewApprovedDate As DateFinPORev,
            TblOrder.FldPrinted, capsule_order.temps_reception_planification As DateReceptionPLANIF, TblOrder.FldPriced,
            CASE
                WHEN ISNULL(TblOrder.FldContractReviewCompletedDate, 0) = 0 THEN capsule_order.temps_reception_planification
                ELSE TblOrder.FldContractReviewCompletedDate
            END As TempsFinRevue,
            (
            SELECT TOP 1 TblOrderXFeredNotifications.FldDate 
            FROM [TCS].[dbo].[TblOrderXFeredNotifications] 
            WHERE TblOrderXFeredNotifications.FldOrdID = TblOrder.FldOrdID 
            ORDER BY TblOrderXFeredNotifications.FldNoLigne
            ) As DatePlanification,
            TblOrder.FldXfer2Sched, 
            TblOrder.FldOrdMod As DateDernierMod, 
            TblOrder.FldOrdStatusDate As DateDernierStatut, 
            TblOrder.FldOrdReq As DateBesoin
        FROM [TCS].[dbo].[TblOrder] 
        RIGHT JOIN [TCS].[dbo].[capsule_order] ON TblOrder.FldJobNB = capsule_order.FldJobNB
        WHERE ? <= TblOrder.FldOrdCre AND TblOrder.FldOrdCre <= ?
    ";

    // 准备参数数组，日期时间格式化为 SQL Server 明确识别的 ISO 8601 格式
    $params = array(
        $dateDebut->format('Y-m-d\TH:i:s').'.000', // 示例：'2023-01-01T00:00:00.000'
        $dateFin->format('Y-m-d\TH:i:s').'.000'   // 示例：'2023-12-31T23:59:59.000'
    );

    // 执行参数化查询
    $query = sqlsrv_query($conn, $sql_q, $params);

    if ($query) {
        // 使用 sqlsrv_has_rows() 检查是否存在结果集
        if (sqlsrv_has_rows($query)) {
            echo "查询返回了结果：<br>";
            while ($result = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)) {
                // 处理每一行结果
                // print_r($result);
                // echo "<br>";
            }
        } else {
            echo "查询未返回任何行。<br>";
            // 调试时可以使用 var_dump(sqlsrv_num_rows($query));
        }
    } else {
        // 查询执行失败，打印错误信息
        echo "查询失败！<br>";
        echo "SQL: " . htmlspecialchars($sql_q) . "<br>";
        print_r(sqlsrv_errors(), true);
        die();
    }
} else {
    // 数据库连接失败
    echo "数据库连接失败！<br>";
    print_r(sqlsrv_errors(), true);
    die();
}
?>