3

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.

Abdulla Nilam
  • 36,589
  • 17
  • 64
  • 85
Felipe Lima
  • 443
  • 1
  • 10
  • 19

3 Answers3

4

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

Felipe Lima
  • 443
  • 1
  • 10
  • 19
3

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;
    }

}
İsmail Kocacan
  • 1,204
  • 13
  • 38
  • 1
    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 – Felipe Lima Feb 05 '18 at 01:51
0
function () {
    $query = $this->db->query("EXEC USP_RoughBlockStockRegister'08/23/2017','08/23/2017',1,1");
    return $query->result();
}
khaleel_jageer
  • 1,404
  • 4
  • 16
  • 37
vijayabalan
  • 91
  • 1
  • 3