How to call a stored procedure in CodeIgniter USIN

Question

I'm bulding my application using Codeigniter and SQL server as my database (I am using the SQLSRV PHP extension to connect to SQL ). The problem happens when I try to call Stored Procedures:

$query = $this->db->query(
          "EXECUTE verificacion_fechas '".$codigo."',".$estado.",'".$llave_maestra."','".$fecha_actual."'"); 

---

Another way I have tried to create the query with less data is the following , however, it is generating an error:

Error Number: 01000 [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Ejecutando SQL directamente, sin cursor. EXECUTE provando15 34,2,'key05','2015-07-22'

I dont really know what im doing wrong. Can someone please help me?

Answer 1

remove the word Execute and it will work.

$query = $this->db->query(
      "verificacion_fechas '".$codigo."',".$estado.",'".$llave_maestra."','".$fecha_actual."'");

Answer 2

Here's my solution:

first way(if you do not use a variable);

$query=$this->MSSQL->query("EXEC ProcedureName");

second way(if you use a variable);

$query =$this->MSSQL->query("EXEC ProcedureName '$var1','$var2','$var3' ");

or

$query =$this->MSSQL->query("EXEC ProcedureName @varName1='$var1',@varName2='$var2',@varName3='$var3' ");

Note:if you get result like empty array add SET NOCOUNT ON to after BEGIN in your procedure.

Answer 3

Here's a sample on how to call a MYSQL STORED PROCEDURE in codeigniter

$this->db->query('CALL procedure_name());