query in PHP is empty but in SQL Server it works

Question

Good afternoon, I have a procedure to run on sql server, if I run directly on SQL it runs normally, returning me the expected line as in the image below:

but when I run codeIgniter 3 (php) it doesn’t error but also brings my Row.

controller:

 $result = $this->posvendas->totalPassagens([getCodeLocal($local), date('Y-m-01 00:00:00'), date('Y-m-d 23:59:59')]); // Não esta retornando a consulta
         echo '<prev>';
         print_r($result);

Model:

     public function totalPassagens(array $array)
{
    $params = [
        3562,
        0,
        '2020-10-01 00:00:00',
        '2020-10-30 23:59:59'
    ];

    $sql = "SET NOCOUNT ON exec whRelOSTotalizarResultadoAgendamento ?, ?, ?, ?";
    $data = $this->db->query($sql, $params);
    return $data->result_array();
}

Exit:

I don’t understand why not return the results

Answer 1

To succeed in sending values to a Stored Procedure has to pass a array of parameters, minimum example:

Table:

Stored Procedure

ALTER PROCEDURE [dbo].[SP_INSERT_SOURCES]   
    @Name VARCHAR(50),
    @Created Date,
    @Status Bit
AS
BEGIN   
    SET NOCOUNT ON;
    INSERT INTO Sources(Name, Created, Status) VALUES(@Name, @Created, @Status);
    SELECT * FROM Sources WHERE Sources.Id = SCOPE_IDENTITY();
END

that script has the function to insert new record, retrieve and send the entered record to the code.

---

How to use it all now with codeigniter?

Sample code:

$params = [
    'StackOverFlow',
    '1992-01-01',
    0
];
$sql = 'EXEC SP_INSERT_SOURCES ?, ?, ?';
$this->db->query($sql, $params);

in the parameters the question mark (?) refers to the amount of variables Stored Procedure.

In your code basically:

public function totalPassagens()
{
    $params = [
        3562,
        0
        '2020-10-01 00:00:00',
        '2020-10-30 23:59:59'
    ];
    $sql = "exec whRelOSTotalizarResultadoAgendamento ?, ?, ?, ?";
    $query = $this->db->query($sql, $params);
    return $query->result_array();
}

Observing: respect the type of information sent to the database, that is, how each field is configured.

^References:

• Call stored Procedure from php codeigniter
• Using MSSQL stored procedures with codeigniter

Answer 2

Good afternoon, I managed to solve as follows, inside the Procedure SET NOCOUNT ON; after Begin instead of putting before the exec command) I could put the sql code here but there are more than 240 lines I believe that is not the case now:

begin  
  SET NOCOUNT ON;