I'm currently trying to execute a stored procedure (two INSERTS and SELECT) via PHP. The stored procedure works (no errors). Values are even inserted into specified tables. We store the result in a JSON object, but it returns an empty array. When we execute a stored procedure containing only SELECT, it returns the correct values. All names have been changed in our tables (some may have been missed, but our code works). Can anyone shed some light on why our array is not filling up?
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 an approximate 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);