0

I need help .. I'm trying to fetch rows from Stored Procedure. but it nothing shows at all in loop ..

I don't know where is my mistake and I hope someone can help me to find it out and teach me the best and good way to fetch data from SP SQL Server.

Here is my code in 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']);
  
 --> Here is how i execute my stored procedure --- 
$get_data  = "exec SP_MCM_CheckInputSchedule '$now_date', '$kode_sub', '$kode_menu', '$akses'";
  
$check_time    = sqlsrv_query($conn,$get_data);
if (!$check_time) { ----------------- The if Here shows Me Good
    echo 'failed';
}else{
    echo 'Good'; <-- even if it return 'Good' but nothing shows from while loop --
    while ($row = sqlsrv_fetch_array($check_time,SQLSRV_FETCH_ASSOC)) {
        echo $row;
    }
}
die;

I treid to fetch using many ways, but nothing work. How I can echo or display the values or the records of stored procedure? Here is the output if I execute the SP in SQL Server:

here is the output if i execute the SP in SQL Server

And here is my SP in my SQL Server that I call/execute from PHP script:

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;
16
  • 6
    WARNING: Your code is dangerous. It is wide open to SQL injection attacks. Always, always, always parametrise your code. Why do we always prefer using parameters in SQL statements? Commented Apr 17, 2023 at 9:18
  • 3
    Also the prefix sp_ is reserved, by Microsoft, for Special / System Procedures. It should not be used for User Procedures. Doing so comes with a performance cost and the risk of your Procedure simply not working one day after an update/upgrade. Either use a different prefix or (possibly better) no prefix at all. Is the sp_ prefix still a no-no? Commented Apr 17, 2023 at 9:19
  • thanks @ThomA i will parametrise to be safe from SQL injection attacks . Commented Apr 17, 2023 at 9:30
  • 1
    echo $row; probably isn't going to work, you need to iterate the array. Commented Apr 17, 2023 at 9:48
  • 1
    @Jafar, four notes: 1) Put SET NOCOUNT ON as first line in your stored procedure; 2) Always use parameteres in your statements to prevent possible SQL injection issues; 3) Where do you assign a value of $akses variable and 4) Try to pass the date value using an unambiguous datetime format (my experience is summarized here). Commented Apr 18, 2023 at 6:55

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.