在 php laravel 中执行存储过程

Question

我正在使用 Laravel 7，我想显示存储过程的结果。我的代码如下。当我在 SQL Server 中执行带有参数的存储过程时，它显示数据。但是在 Laravel 应用程序数据中没有显示。

请帮我找出问题所在。

$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);

     }

Answer 1

始终尝试在语句中使用参数以防止可能的 SQL 注入问题。作为附加说明，当您将日期值传递给 SQL Server 时，请使用明确的日期格式：使用适用于 SQL Server 的 PHP 驱动程序的示例：<?php// Connection$serverName = env("DB_HOST");$connectionInfo = array(&nbsp; &nbsp; "Database"=>env("DB_DATABASE"),&nbsp;&nbsp; &nbsp; "UID"=>env("DB_USERNAME"),&nbsp;&nbsp; &nbsp; "PWD"=>env("DB_PASSWORD"));$conn = sqlsrv_connect($serverName, $connectionInfo);if ($conn === false) {&nbsp; &nbsp; die(print_r(sqlsrv_errors(), true));}// Statement$employee = '000010993';$datefrom = '20200601';&nbsp;$dateto&nbsp; &nbsp;= '20200610';$tsql&nbsp; &nbsp; &nbsp;= "EXEC USP_Daily_TA_Punching_Detailswith_OT ?, ?, ?";$params&nbsp; &nbsp;= array($employee, $datefrom, $dateto);$getResults = sqlsrv_query($conn, $tsql, $params);if ($getResults === false) {&nbsp; &nbsp; die(print_r(sqlsrv_errors(), true));}&nbsp; &nbsp;// Results$data = array();do {&nbsp; &nbsp; while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {&nbsp; &nbsp; &nbsp; &nbsp; $data[] = $row;&nbsp; &nbsp; }} while (sqlsrv_next_result($getResults));// Endsqlsrv_free_stmt($getResults);sqlsrv_close($conn);$total_row = count($data);?>&nbsp; &nbsp;使用 Laravel 的示例：<?php...$employee = '000010993';$datefrom = '20200601';&nbsp;$dateto&nbsp; &nbsp;= '20200610';DB::select("SET NOCOUNT ON; EXEC USP_Daily_TA_Punching_Detailswith_OT ?, ?, ?", array($employee, $datefrom, $dateto));...?>

Answer 2

试试下面的方法来调用 laravel 中的存储过程DB::select("call&nbsp;USP_Daily_TA_Punching_Detailswith_OT('".$employee."','".$datefrom."','".$dateto."')");