Display results from Stored Procedure using SQL Server and PHP

Question

I am trying to display the output of the stored procedure using PHP and SQL server. The stored procedure is a SELECT statement. As of now, I end up in the else condition that displays the success message, but I am not able to show the results from the query. Here is the function:

  function account_search_sp($account_number,$occupant_code,$name,$address,$bill_code,$utility_code){
   global $db;

   sqlsrv_configure("WarningsReturnAsErrors", 0);

   $sql = "EXEC sp_cigar_account_search @AcctNo= ?, @OccupantCode= ?, @Name= ?, @Address= ?, @BillCode= ?, @UtilityType= ?";

   $procedure_params = array(
   array(&$account_number, SQLSRV_PARAM_IN),
   array(&$occupant_code, SQLSRV_PARAM_IN),
   array(&$name, SQLSRV_PARAM_IN),
   array(&$address, SQLSRV_PARAM_IN),
   array(&$bill_code, SQLSRV_PARAM_IN),
   array(&$utility_code, SQLSRV_PARAM_IN)
   );

   $stmt = sqlsrv_prepare($db, $sql, $procedure_params);

   $result = sqlsrv_execute($stmt);

   if( !$result ) {
     //Show errors
     echo "Die error <br>";
     die( print_r( sqlsrv_errors(), true));

   }else{
     echo "<br><h3>Success</h3><br>";
     sqlsrv_next_result($stmt);
   }

return $stmt; }

If I run the stored procedure in SSMS for instance with a 200 as the account number, I get data back.

I am using PHP 7.4

Any help is appreciated. Please let me know if I need to include more information.

Answer 1

If I understand the question correctly, you need to fetch the data using sqlsrv_ferch_array() or sqlsrv_fetch_object():

<?
function account_search_sp($account_number, $occupant_code, $name, $address, $bill_code, $utility_code) {
   global $db;

   sqlsrv_configure("WarningsReturnAsErrors", 0);

   $sql = "EXEC sp_cigar_account_search @AcctNo= ?, @OccupantCode= ?, @Name= ?, @Address= ?, @BillCode= ?, @UtilityType= ?";

   $procedure_params = array(
      array(&$account_number, SQLSRV_PARAM_IN),
      array(&$occupant_code, SQLSRV_PARAM_IN),
      array(&$name, SQLSRV_PARAM_IN),
      array(&$address, SQLSRV_PARAM_IN),
      array(&$bill_code, SQLSRV_PARAM_IN),
      array(&$utility_code, SQLSRV_PARAM_IN)
   );

   $stmt = sqlsrv_prepare($db, $sql, $procedure_params);
   if ($stmt === false) {
      echo "Die error <br>";
      die( print_r( sqlsrv_errors(), true));
   }
   
   if (sqlsrv_execute($stmt) === false) {
      echo "Die error <br>";
      die( print_r( sqlsrv_errors(), true));
   }
   
   echo "<br><h3>Success</h3><br>";
   $data = array();
   do {
      while ($row = sqlsrv_fetch_array($stmt)) {
         $data[] = $row;
      }
   } while (sqlsrv_next_result($stmt)); 

   return $data; 
}
?>