Executing stored procedure with output

Hi All,

Kindly help me on my issued regarding stored procedure.

I have a query (SP) on SSMS which is working fine but when running on codeigniter it returns blank result.

SSMS



Codeigniter

Model
public function call_receiving($receipt_key,$line_no,$accident_code,$sku,$pid,$qty)
  {
     $this->output->enable_profiler(TRUE);                           //CI Profiler
     $query = $this->db->query("
     begin
     DECLARE @n_err int,
           @c_errmsg varchar(255)
     
     EXEC   [dbo].[SCH_SME_INBOUND_RECEIVING_SCAN]
           @c_receiptkey = N'{$receipt_key}',
           @c_receiptlinenumber = N'{$line_no}',
           @c_accidentcode = N'{$accident_code}',
           @c_scansku = N'{$sku}',
           @c_id = N'{$pid}',
           @n_qty = {$qty},
           @c_userid = N'{$rfuser}',
           @n_err = @n_err OUTPUT,
           @c_errmsg = @c_errmsg OUTPUT;
     
     SELECT @n_err as N'@n_err',
           @c_errmsg as N'@c_errmsg'
      end
      ");
     return $query->result();
  }
Controller
public function call_receiving()
  {
     $receipt_key = $this->input->post('receipt_key');
     $line_no = $this->input->post('line_no');
     $accident_code = $this->input->post('accident_code');
     $sku = $this->input->post('sku');
     $pid = $this->input->post('pid');
     $qty = $this->input->post('qty');
     $result = $this->main->call_receiving($receipt_key,$line_no,$accident_code,$sku,$pid,$qty);
     //$result = $this->sp->sqlsrv_runSP("SCH_SME_INBOUND_RECEIVING_SCAN",$params);
     echo json_encode(array('status'=>true,'result'=>$result));

  }


Result
{"status":true,"result":[]}

@johnine,

A few questions... What database are you using? Version? Why didn't you create the stored procedure in the database instead and then do a call to it in Codeigniter?

@johnine,

A few questions... What database are you using? Version? Why didn't you create the stored procedure in the database instead and then do a call to it in Codeigniter?

Hello Thanks for your time on replying appreciate your effort.

I'm using Sql Server 2008 R2/ PHP 5.6
Yes I created Stored Procedure ([dbo].[SCH_SME_INBOUND_RECEIVING_SCAN]) it runs on my php code viewed on profiler but my problem is I cant get the output err_msg as shown in the screenshot, it returns null.