php - 存储过程没有执行所有数据

Question

在我用 PHP 语言创建的 Web 服务中执行存储过程时遇到问题。如果我直接在 SQL Server Management Studio 中执行下面的 SP，它可以完美运行。但是当我使用 PHP 从我的 Web 服务执行 SP 时，它只执行我在 SP 中创建的循环中的一些数据。

我得到了一个服务表作为主人房数据。在下面的那个 SP 中，我想检查我一一输入到存储过程的日期范围内的可用房间。

例如，我在服务表中有 6 个房间，我想用光标循环检查它。当我在 Web 服务中执行 SP 时，它只循环执行 4 个房间，但如果我直接在管理工作室中执行 SP，它可以完美运行。

有人可以帮我解决这个问题吗？

谢谢你。

Create Procedure GetAvailableRoom
    @Date1 Datetime,
    @Date2 Datetime,
    @UserID Varchar(15)
AS
Begin
    
    Delete From Room_Availability Where UserID = @UserID 
    
    Declare @KL Varchar(50)

    Declare Cursor_RA Cursor For SELECT Service_Code FROM Services Where Status = 'ROOM' Order By Service_Code

    Open Cursor_RA

    Fetch Next From Cursor_RA Into @KL

    While @@FETCH_STATUS = 0 
        Begin
            print @KL
            
            Declare @MyDate DateTime
            Set @MyDate = @Date1

            Declare @Diff Int
            Set @Diff = DateDiff(Day, @Date1, @Date2)

            Declare @Counter Int
            Set @Counter = 0

            print @MyDate
            print @Diff
            print @Counter

            While (@Counter <= @Diff)
                Begin
                    Print 'My Date is ' + Convert(Varchar, format(@MyDate,'yyyy-MM-dd'))

                    if exists(SELECT @KL, format(@MyDate,'yyyy-MM-dd 00:0:00'), Customers.Name + ' | ' + Customers.HP, @UserID
                                FROM Reservation INNER JOIN Reservation_Details ON Reservation.Reservation_Code = Reservation_Details.Reservation_Code 
                                                INNER JOIN Customers ON Reservation.Customer_ID = Customers.Customer_ID 
                                WHERE Reservation_Details.Kode_Service = @KL AND 
                                        Reservation_Details.Checkin <= format(@MyDate,'yyyy-MM-dd 23:59:59') AND 
                                        Reservation_Details.Checkout >= format(@MyDate,'yyyy-MM-dd 00:0:00'))
                        Begin
                            print 'exists'

                            Insert Into Room_Availability (Service_Code,Service_Date,Customer,UserID)
                            SELECT @KL, format(@MyDate,'yyyy-MM-dd 00:0:00'), Customers.Name + ' | ' + Customers.HP, @UserID 
                            FROM Reservation INNER JOIN Reservation_Details ON Reservation.Reservation_Code = Reservation_Details.Reservation_Code 
                                            INNER JOIN Customers ON Reservation.Customer_ID = Customers.Customer_ID 
                            WHERE Reservation_Details.Service_Code = @KL AND 
                                    Reservation_Details.Checkin <= format(@MyDate,'yyyy-MM-dd 23:59:59') AND 
                                    Reservation_Details.Checkout >= format(@MyDate,'yyyy-MM-dd 00:0:00')
                        End
                    Else
                        Begin
                            print 'not exists'

                            Insert Into Room_Availability (Service_Code,Service_Date,Customer,UserID)
                            Values(@KL, format(@MyDate,'yyyy-MM-dd 00:0:00'), 'EMPTY', @UserID)
                        End

                    Set @MyDate = dateadd(dd,1,@MyDate)
                    Set @Counter = @Counter + 1
                End
            
            Fetch Next From Cursor_RA Into @KL
        End

    Close Cursor_RA
    Deallocate Cursor_RA
End

这是网络服务代码。

<?php

include 'settings.php';

ini_set('mssql.connect_timeout',15);
$sqlconn = sqlsrv_connect($serverName, $connectionOptions);
if ($sqlconn === false)
    die(FormatErrors(sqlsrv_errors()));

$tsql = "GetAvailableRoom '" . $_POST['Date1'] . "','" . $_POST['Date2'] . "','" . $_POST['UserID'] . "'";
//echo $tsql;

$cursorType = array("Scrollable" => SQLSRV_CURSOR_DYNAMIC);
$getexec = sqlsrv_query($sqlconn, $tsql, null, $cursorType);

if ($getexec === false)
    die(FormatErrors(sqlsrv_errors()));

sqlsrv_free_stmt($getexec);
sqlsrv_close($sqlconn);

标签： phpsql-serverflutter