PHP调用SQL Server存储过程无法获取数据问题求助

问题描述 #

尝试通过PHP从SQL Server存储过程获取数据，sqlsrv_query返回成功（输出'Good'），但循环中完全没有数据输出。已确认存储过程在SQL Server中执行能正常返回结果。

提供的代码与信息 #

PHP代码 #

$connectionInfo = array( "UID"=>"XXXX", "PWD"=>"xxxxxx", "Database"=>"xxxxx" );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ){
    echo "Could not connect to server.\n";
    die( print_r( sqlsrv_errors(), true));
}

$now_date  = date('Ymd H:i:s');
$kode_menu = trim($get_menu_n_sub['Kd_menu']);
$kode_sub  = trim($get_menu_n_sub['kd_sub']);
  
// 执行存储过程
$get_data  = "exec SP_MCM_CheckInputSchedule '$now_date', '$kode_sub', '$kode_menu', '$akses'";
  
$check_time    = sqlsrv_query($conn,$get_data);
if (!$check_time) {
    echo 'failed';
}else{
    echo 'Good'; // 这里输出Good，但循环无内容
    while ($row = sqlsrv_fetch_array($check_time,SQLSRV_FETCH_ASSOC)) {
        echo $row;
    }
}
die;

SQL Server存储过程执行结果 #

在SQL Server中执行该存储过程，能正常返回包含boleh、toleran、kode_jadwal、nama_jadwal、Keterangan字段的结果集。

存储过程代码 #

USE [DB_Name]
GO
/****** Object:  StoredProcedure [dbo].[SP_MCM_CheckInputSchedule]    
Script Date: 17/04/2023 16.53.19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_MCM_CheckInputSchedule](@dWaktu as datetime, @kode_sub as int, @kode_menu as int, @akses as varchar(10))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- SET NOCOUNT ON;
declare @kode_jadwal char(5);
declare @nama_jadwal char(30);
declare @white_list bit;
declare @boleh bit;
declare @toleran bit;
declare @tgl date;
declare @jam time;

set @tgl = CAST(@dWaktu as date)
set @jam = CAST(@dWaktu as time)

create table #list_table ( kode_jadwal char(5), nama_jadwal char(30), boleh bit, toleran bit )

select a.kode_jadwal, a.nama_jadwal, a.white_list, d.by_datetime, 
d.waktu_awal, d.waktu_akhir,
cast(d.waktu_awal as date) as tgl_awal, cast(d.waktu_akhir as date) as tgl_akhir,
cast(d.waktu_awal as time) as jam_awal, cast(d.waktu_akhir as time) as jam_akhir,
(case when d.waktu_toleran is null then d.waktu_akhir else d.waktu_toleran end) as waktu_toleran,
(case when d.waktu_toleran is null then cast(d.waktu_akhir as date) else cast(d.waktu_toleran as date) end) as tgl_toleran,
(case when d.waktu_toleran is null then cast(d.waktu_akhir as time) else cast(d.waktu_toleran as time) end) as jam_toleran
into #all_available
from mcmjadwalh as a
inner join mcmjadwald_menu as b on a.kode_jadwal=b.kode_jadwal
inner join mcmjadwald_user as c on c.kode_jadwal=a.kode_jadwal
inner join mcmjadwald_tgl as d on d.kode_jadwal=a.kode_jadwal
where b.kd_menu=@kode_menu and b.kd_submenu=@kode_sub and c.role_id=@akses

-- White List
select distinct a.kode_jadwal, a.nama_jadwal, 1 as boleh, 0 as toleran
into #white_list
from #all_available as a
where a.white_list=1 and ((a.by_datetime=1 and (@tgl between a.tgl_awal and a.tgl_akhir) and 
(@jam between a.jam_awal and a.jam_akhir)) or
(a.by_datetime=0 and (@dWaktu between a.waktu_awal and a.waktu_akhir)))

-- Black List
select distinct a.kode_jadwal, a.nama_jadwal, 0 as boleh, 0 as toleran
into #black_list
from #all_available as a
where a.white_list=0 and ((a.by_datetime=1 and (@tgl between a.tgl_awal and a.tgl_akhir) and 
(@jam between a.jam_awal and a.jam_akhir)) or
(a.by_datetime=0 and (@dWaktu between a.waktu_awal and a.waktu_akhir)))

-- Toleran List
select distinct a.kode_jadwal, a.nama_jadwal, 0 as boleh, 1 as toleran
into #toleran_list
from #all_available as a
where a.white_list=1 and (
(a.by_datetime=1 and 
(((@tgl between a.tgl_awal and a.tgl_toleran) and (@jam between a.jam_akhir and a.jam_toleran)) or
 ((@tgl between a.tgl_akhir and a.tgl_toleran) and (@jam between a.jam_awal and a.jam_toleran)))
) or
(a.by_datetime=0 and (@dWaktu between a.waktu_akhir and a.waktu_toleran)))

-- Unlist
select distinct a.kode_jadwal, a.nama_jadwal, (case a.white_list 
                                                when 0 then 1 
                                                when 1 then 0 end) as boleh, 0 as toleran
into #unlist
from #all_available as a
left join #white_list as b on a.kode_jadwal=b.kode_jadwal
left join #black_list as c on a.kode_jadwal=c.kode_jadwal
left join #toleran_list as d on a.kode_jadwal=d.kode_jadwal
where b.kode_jadwal is null and c.kode_jadwal is null and d.kode_jadwal is null

-- Gabungan
select top 1 *
into #hasil
from (  select a.kode_jadwal, a.nama_jadwal, a.boleh, a.toleran
        from ( select *
                from #white_list as a1
                union all
                select *
                from #black_list as b1
                union all
                select *
                from #toleran_list as c1
                union all
                select *
                from #unlist as d1 ) as a ) as b
order by b.boleh, b.toleran, b.kode_jadwal

if @@ROWCOUNT=0
    begin
        Select 0 as boleh, 0 as toleran, '' as kode_jadwal, '' as nama_jadwal, 
        'Tidak ada Jadwal yang di assign utk Role ID dan Menu ID ini' as Keterangan
    end
else
    begin
        select a.boleh, a.toleran, a.kode_jadwal, a.nama_jadwal,
        (case a.toleran when 1 then 'Masuk Batas Toleransi' 
            else (case a.boleh when 1 then 'Valid' 
                    else 'Melanggar Aturan Jadwal' end) end) as Keterangan
        from #hasil as a
    end

--drop table #hasil;
drop table #unlist;
drop table #white_list;
drop table #black_list;
drop table #toleran_list;
drop table #all_available;
drop table #list_table;

END;

问题排查与解决方法 #

1. 日期格式不匹配问题 #

PHP代码中用date('Ymd H:i:s')生成的日期格式为20240520 12:30:00，但SQL Server的datetime类型默认接受YYYY-MM-DD HH:MI:SS格式，直接拼接字符串会导致参数解析错误，存储过程实际无数据返回。

解决方法：
使用参数化查询，自动处理类型转换：

$params = array(
    array($now_date, SQLSRV_PARAM_IN),
    array($kode_sub, SQLSRV_PARAM_IN),
    array($kode_menu, SQLSRV_PARAM_IN),
    array($akses, SQLSRV_PARAM_IN)
);
$get_data = "{call SP_MCM_CheckInputSchedule(?, ?, ?, ?)}";
$check_time = sqlsrv_query($conn, $get_data, $params);

2. 存储过程额外结果集干扰 #

存储过程中创建临时表、执行SELECT INTO会产生多个空结果集，sqlsrv_fetch_array可能停留在空结果集，无法获取最终查询结果。

解决方法：

• 在存储过程开头添加SET NOCOUNT ON;，避免返回受影响行数的结果集；
• 用sqlsrv_next_result跳过空结果集：

if (!$check_time) {
    echo 'failed';
    die(print_r(sqlsrv_errors(), true));
} else {
    echo 'Good';
    // 遍历所有结果集
    do {
        while ($row = sqlsrv_fetch_array($check_time, SQLSRV_FETCH_ASSOC)) {
            // 按字段输出或打印数组
            print_r($row);
            echo "boleh: {$row['boleh']}, Keterangan: {$row['Keterangan']}<br>";
        }
    } while (sqlsrv_next_result($check_time));
}

3. 直接输出数组的错误 #

原代码中echo $row;错误，数组直接echo只会输出Array字符串，需用print_r($row)、var_dump($row)或按字段输出。

调用SQL Server存储过程的最优实践 #

• 强制使用参数化查询：避免SQL注入，自动处理数据类型转换；
• 开启SET NOCOUNT ON：减少不必要的结果集干扰；
• 处理多结果集：存储过程含多个SELECT时，用sqlsrv_next_result遍历所有结果集；
• 完善错误处理：在sqlsrv_query和sqlsrv_fetch_array后添加错误检查，用sqlsrv_errors()获取详细信息；
• 释放资源：使用sqlsrv_free_stmt($check_time)释放语句资源，sqlsrv_close($conn)关闭连接，避免内存泄漏。

内容的提问来源于stack exchange，提问作者Ramzi