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