Database Exception

Yii 2.0 General Discussions
1

Need help!!

Hallo.

how to call my store procedure?

my store procedure code :

ALTER procedure [dbo].[spSetSLoc]

(@sLoc nvarchar(4), @description nvarchar(50))

as

begin

declare @rowVer rowversion;





begin try


	if exists(select SLoc from SLocs where SLoc = @sLoc)


	begin


		select @rowVer = RowVer from SLocs where SLoc = @sLoc;





		update SLocs


		set Description = @description


		where SLoc = @sLoc and RowVer = @rowVer;





		if(@@rowcount = 0)


		begin


			raiserror('Data has been updated by other user', 16, 1);


		end


	end


	else


	begin


		insert into SLocs(SLoc, Description)


		values(@sLoc, @description);


	end


end try


begin catch


	declare @errorMessage nvarchar(max);


	declare @errorProcedure nvarchar(max);


	declare @error nvarchar(max);


	


	select @errorMessage = ERROR_MESSAGE(), @errorProcedure = ERROR_PROCEDURE();


	set @error = 'Error on procedure: ' + @errorProcedure + '.' + CHAR(13) + @errorMessage;


	raiserror(@error, 11, 1);


end catch

end

here my code to create in controller :

$command = Yii::$app->db->createCommand('CALL spSetSloc (

   :Sloc,  


   :Description,  


   :RowVer )');


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


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


    $command->bindValue(':RowVer', T_DEFAULT);


    $result = $command->execute();

and the result, i got error like this

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ‘@P1’.

The SQL being executed was: CALL spSetSloc (

‘BC033’,

‘BC033’,

330 )

Error Info: Array

(

[0] => 42000


[1] => 102


[2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '@P1'.

)

How to fix it??

thanks

2

fixed :D

i used this code.

public function actionCreate() {

    $model = new Slocs();


    if ($model->load(Yii::$app->request->post())) {


        try {





            $command = Yii::$app->db->createCommand("spSetSLoc @Sloc =:Sloc ,@Description =:Description");


            $command->bindParam(":Sloc", $Sloc);


            $command->bindParam(":Description", $Description);


            $command->execute();


            $model->save();


        } catch (Exception $e) {


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


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


        }


        return $this->redirect(['view', 'id' => $model->SLoc]);


    }


    return $this->render('create', [


                'model' => $model,


    ]);


}

any suggest for this??