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

Question

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?

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

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

Answer 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.