Call stored procedure from php codeigniter

Question

Looking for one week and did not found some content that I can use to execute a stored procedure. Scenario:

I have copied the code that runs on SQL Server 2014 management in the Profiler window.

I opened the SQL Studio and HeidiSQL and run the queries and it runs ok and returns me the expected result.

When I tried to paste and modify this content to get the same result as I have on the SQL clients it did not run like it does in the SQL clients. How can I achieve this?

eg: In my SQL I have 128 vars that I have to declare to run the stored procedure but to be more simple I will post only one var having in mind that sintaxe is the same to one or 128 vars right?

declare @var01 varchar(1000) set @var01='.$var01.'

EXEC
stored_procedure_y
@var01 output

SELECT
@var01 AS any_value

I have tried many ways to execute the SQL commands with no success. Some examples that I tried to run:

• last

  $declare_vars = 'declare @var01 varchar(1000) set @var01='.$var01.';
  $exec_sp = 'EXEC operacao_sitef @var01 output;
  $select_retorno = 'SELECT @var01 AS any_value;
  
  $queryDeclare = $this->db->query($declare_vars);
  
  $queryExec = $this->db->query($exec_sp);
      $queryRetorno = $this->db->query($select_retorno);
  
      return $queryRetorno;
  

  Do not work.

• other

  $query = $this->db->query('
  declare @var01 varchar(1000) set @var01='.$var01.'
  EXEC
  operacao_sitef
  @var01 output
  SELECT
  @var01 AS any_value
   ');
   return $query;
  

Also did not work. Any help would be appreciated. I'm newbie with SP's and don't know how to call this using codeigniter and collect the results to finalize the operation.

Solution 1

Glad to say that I will maintain my job.

Here goes the post that solved my problem. Remembering I'm working with codeigniter on php and mssql database:

In model:

public function execute_sp($var1 = NULL,$var2 = NULL, $var3 = NULL, $var4 = NULL, $var5 = NULL, $var6 = NULL, $var7 = NULL, $var8 = NULL, $var9 = NULL, $var10 = NULL){
$sp = "stored_procedure_name ?,?,?,?,?,?,?,?,?,? "; //No exec or call needed

//No @ needed.  Codeigniter gets it right either way
$params = array(
'PARAM_1' => NULL,
'PARAM_2' => NULL,
'PARAM_3' => NULL,
'PARAM_4' => NULL,
'PARAM_5' => NULL,
'PARAM_6' => NULL,
'PARAM_7' => NULL,
'PARAM_8' => NULL,
'PARAM_9' => NULL,
'PARAM_10' =>NULL);

$result = $this->db->query($sp,$params);

In controller:

$var1 = 'value';
$var2 = 'value';
$var3 = 'value';
$var4 = 'value';
$var5 = 'value';
$var6 = 'value';
$var7 = 'value';
$var8 = 'value';
$var9 = 'value';
$var10 = 'value';

$this->model->sp($var1, $var2, $var3, $var4, $var5, $var6, $var7, $var8, $var9, $var10);

It works!

source that helped so much: Issue executing stored procedure from PHP to a Microsoft SQL SERVER reply from user @Ulises Burlando

Solution 2

I solved like this.

I'm using PDO driver.

simple_query method is returning PDOStatement Object and one important point SET NOCOUNT ON

class Pump_Model extends CI_Model
{
    public function getPumpStatisticSpById($pumpId)
    {
        $result = $this->db->simple_query("SET NOCOUNT ON 
                            DECLARE @return_value int
                            EXEC    @return_value = [dbo].[spPumpStatistics]
                                    @PumpID = $pumpId")->fetch(PDO::FETCH_OBJ);
        return $result;
    }

}

Comments

[Felipe Lima]

Good to know. May help people in PDO cases. In my case I could not use PDO because system was using MSSQL driver and EXEC was not a option using Amazon RDS

[Nilesh Daldra]

if we want to skip optional param then what we can do here?

[Felipe Lima]

@NileshDaldra I don't know your use scenario but I guess you could let the PHP var set to NULL in controller