在PHP中插入和选择存储过程

I am currently trying to execute a stored procedure(Two INSERTS and a SELECT) through PHP. The stored procedure works (no errors are thrown). The values are even inserted into the given tables. We are storing the result in a JSON object but it is returning an empty array. When we execute the stored procedure containing only a SELECT it returns the correct values. All the names have been changed in our tables (may have missed some but our code runs). Can anyone shed light on why our array is not populating?

我目前正在尝试通过PHP执行一个存储过程(两个插入和一个选择)。存储过程工作(不会抛出错误)。这些值甚至被插入到给定的表中。我们将结果存储在JSON对象中，但它返回的是一个空数组。当我们执行只包含一个SELECT的存储过程时，它返回正确的值。我们的表中所有的名字都已经更改(可能漏掉了一些，但是我们的代码正在运行)。有人能解释为什么我们的阵列不被填充吗?

Here is our stored procedure

这是我们的存储过程。

ALTER procedure 
As
BEGIN

INSERT INTO TABLE1
(Loan_ID, Doc_ID, Borrower_Name, Docs_Drawn, Funder, First_Payment, Interest_Rate, Loan_Amount)
Select Loan_ID, 
        (SELECT (COUNT(*)) FROM TABLE WHERE A.Loan_ID = Loan_ID) AS Doc_ID,
        Borrower ,[Date_Docs_Drawn]
      ,[Funder]
      ,[Payment_Date]
      ,[Interest_Rate]
      ,[Loan_Amount] from VIEW A

 INSERT INTO TABLE2
(Loan_ID, Submitted_By, Event_Class, Event_Type, Event_Date, Doc_ID)
SELECT A.[Loan_ID], 
    'Program' AS Submitted_By
    ,'Collateral' AS Event_Class
      , 'Outstanding' AS Event_Type
     , CURRENT_TIMESTAMP AS Event_Date 
     , Doc_ID                                       
  FROM TABLE1 AS A
  WHERE NOT (A.Loan_ID = ANY (SELECT Loan_ID FROM TABLE2))

    SELECT [Loan_ID],[Doc_ID],[Borrower_Name]
      ,[Funder]
      ,[Docs_Drawn]
      ,[First_Payment]
      ,[Loan_Amount]
      ,[Interest_Rate]
    FROM TABLE1
    WHERE Loan_ID <> ALL
    (SELECT Loan_ID FROM TABLE2
    WHERE Event_Type <> 'Outstanding')
    ORDER BY Funder

END

here is a rough sketch of our PHP

这是我们的PHP的一个粗略的草图

$query = "exec storedProcedure";
$result=sqlsrv_query($conn, $query);
$table = array();
while($row = sqlsrv_fetch_array($result)) {
    $table[] = $row;
}
echo json_encode($table);

1 个解决方案

#1

Try using SET NOCOUNT ON in your stored procedure. You are probably being limited by the results returned to the PHP from the SQL Server PHP driver, so you aren't getting the data from the SELECT statement.

尝试在存储过程中使用SET NOCOUNT。您可能受到SQL Server PHP驱动程序返回到PHP的结果的限制，因此您无法从SELECT语句获取数据。