How To Call A Stored Procedure With 2 Parameters?

Yii 1.1 General Discussion
1

How to call a stored procedure with 2 parameters?


$b = Yii::app()->db2->getCommandBuilder();


                $hdr = 2925;

                $one = 1;

                

        $cmd = $b->createSqlCommand('CALL SP_MO_FILE_CREATE_CH(:hdr_id, :one);')

                        ->bindParam(':hdr_id',$hdr)

                        ->bindParam(':one',$one)

                        ->execute();

causes an error

CDbCommand не удалось подготовить SQL-запрос: SQLSTATE[HY000]: General error: -104 Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 1 CALL

2

I recommend using the facade design pattern which you can divide your SQL connections and control so you can call them from different places without repeating the code.

The code would be as follows:




.../protected/facade/ExampleFacade.php







<?php

   class ExampleFacade {

      public static function getExample($first, $second) {

         try {

            $sql =  '{ CALL SP_Example_Get (@firstSQL =:first, @secondSQL =:second)}' ;

            

            $command = Yii::app()->db->createCommand($sql);

            $command->bindParam(":first", $first, PDO::PARAM_INT);

            $command->bindParam(":second", $second, PDO::PARAM_INT);

            $list = $command->queryAll();

         }catch (Exception $e) {

            Log::trace("Error : ".$e);

            throw new Exception("Error : ".$e);

         }

         return $list;

      }

   }

?>

To run it from your controller:




$list = ExampleFacade::getExample($first, $second);

PD: I’m using SQL Server in the example. you must change the way your database engine works.

3

Thanks, joel.campusano!