Executing stored procedure in php laravel

I am using Laravel 7 and I want to show the results from the stored procedure. My code is given below. When I execute the stored procedure with parameters in SQL Server, it’s showing data. But in Laravel application data is not showing.

Please, help me to find the problem.

$serverName = env("DB_HOST");
$connectionInfo = array( "Database"=>env("DB_DATABASE"), "UID"=>env("DB_USERNAME"), "PWD"=>env("DB_PASSWORD") );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
  die( print_r( sqlsrv_errors(), true));
}
$tsql= " EXEC USP_Daily_TA_Punching_Detailswith_OT '$employee','$datefrom','$dateto'";
$getResults= sqlsrv_query($conn, $tsql);
      $data = array();
      if ($getResults == FALSE)
      {
      echo '';
      }
      else {
       //$data[] ='';
      do
      {
        while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC))
        {
         $data[] = $row;
        }
      }
       while (sqlsrv_next_result($getResults));
      }
      if(count($data)>0){
       sqlsrv_free_stmt($getResults);
      $total_row = count($data);
     }

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Always try to use parameters in your statements to prevent possible SQL injection issues. As an additional note, use unambiguous date format, when you pass date values to SQL Server:

Example using PHP Driver for SQL Server:

<?php
// Connection
$serverName = env("DB_HOST");
$connectionInfo = array(
    "Database"=>env("DB_DATABASE"), 
    "UID"=>env("DB_USERNAME"), 
    "PWD"=>env("DB_PASSWORD")
);
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn === false) {
    die(print_r(sqlsrv_errors(), true));
}

// Statement
$employee = '000010993';
$datefrom = '20200601'; 
$dateto   = '20200610';
$tsql     = "EXEC USP_Daily_TA_Punching_Detailswith_OT ?, ?, ?";
$params   = array($employee, $datefrom, $dateto);
$getResults = sqlsrv_query($conn, $tsql, $params);
if ($getResults === false) {
    die(print_r(sqlsrv_errors(), true));
}   

// Results
$data = array();
do {
    while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
        $data[] = $row;
    }
} while (sqlsrv_next_result($getResults));

// End
sqlsrv_free_stmt($getResults);
sqlsrv_close($conn);
$total_row = count($data);
?>

Example using Laravel:
<?php

...
$employee = '000010993';
$datefrom = '20200601'; 
$dateto   = '20200610';
DB::select("SET NOCOUNT ON; EXEC USP_Daily_TA_Punching_Detailswith_OT ?, ?, ?", array($employee, $datefrom, $dateto));

...
?>

Method 2

Try the below to call the store procedure in laravel

DB::select("call USP_Daily_TA_Punching_Detailswith_OT('".$employee."','".$datefrom."','".$dateto."')");


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x