Error Call Store Procedure Ms Sql Server

hello,

I have code like below :




public function actionLaporanKSPA(){

		$NBA = '01-15-26400';

		$UserID = 'uli';


		  $command = Yii::app()->db->createCommand('call rKSPA (:NBA,:UserID )');  

		  $command->bindParam('NBA', $NBA);   

		  $command->bindParam('UserID', $UserID);  

		  $result = $command ->execute();  

		

	}



but I get error like below after executed the action :




 CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 102 General SQL Server error: Check messages from the SQL Server [102] (severity 15) [(null)]. The SQL statement executed was: call rKSPA (:NBA,:UserID )


/var/www/html/yii/framework/db/CDbCommand.php(357)


345         {

346             if($this->_connection->enableProfiling)

347                 Yii::endProfile('system.db.CDbCommand.execute('.$this->getText().$par.')','system.db.CDbCommand.execute');

348 

349             $errorInfo=$e instanceof PDOException ? $e->errorInfo : null;

350             $message=$e->getMessage();

351             Yii::log(Yii::t('yii','CDbCommand::execute() failed: {error}. The SQL statement executed was: {sql}.',

352                 array('{error}'=>$message, '{sql}'=>$this->getText().$par)),CLogger::LEVEL_ERROR,'system.db.CDbCommand');

353 

354             if(YII_DEBUG)

355                 $message.='. The SQL statement executed was: '.$this->getText().$par;

356 

357             throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',

358                 array('{error}'=>$message)),(int)$e->getCode(),$errorInfo);

359         }

360     }

361 

362     /**

363      * Executes the SQL statement and returns query result.

364      * This method is for executing an SQL query that returns result set.

365      * @param array $params input parameters (name=>value) for the SQL execution. This is an alternative

366      * to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing

367      * them in this way can improve the performance. Note that if you pass parameters in this way,

368      * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.

369      * Please also note that all values are treated as strings in this case, if you need them to be handled as






please, help me

Maybe with exec?




$command = $db->createCommand('exec rKSPA :NBA,:UserID');

...



Some examples for MSSql stored procedures working for me:




 $sql = 'EXEC myStoredProc @Param1=:Param1,@Param2=:Param2';

 $command = $db->createCommand($sql);

 $command->bindParam(':Param1', $Param1);

 $command->bindParam(':Param2', $Param2);


  $command->queryAll(); //queryRow()






 $sql = 'DECLARE @errorCode INTEGER; EXEC @errorCode = myStoredProc @Param1=:Param1,@Param2=:Param2';

 $command = $db->createCommand($sql);

 $command->bindParam(':Param1', $Param1);

 $command->bindParam(':Param2', $Param2);


  $command->queryScalar(); //the errorcode



thanks joblo for your reply,

I will try it soon,

Just detected in your code a typo?

Missing ":" in your bindParam;





//$command->bindParam('NBA', $NBA); wrong  


//must be

$command->bindParam(':NBA', $NBA);