I have a stored procedure to update a column(
LiveStatus
LiveStatus
DeviceID
livestatus
DeviceID
DELIMITER $$
CREATE DEFINER=`testmysql`@`%` PROCEDURE `PDADeviceAssign`(
IN DeviceID varchar(50),
IN EmpID varchar(50),
IN PlazaID varchar(50),
IN LaneID varchar(50),
IN AssignDateTime varchar(50),
IN AssignTranID varchar(50),
IN AssignedBy varchar(50),
IN Start_at varchar(50),
IN End_at varchar(50),
IN IsNextDay varchar(50),
IN ClientID varchar(50),
IN Description varchar(50)
)
BEGIN
declare Sno int;
set Sno=(SELECT MAX(Sno) FROM posassignment WHERE DeviceID=DeviceID);
update posassignment set LiveStatus ='N' where Sno=Sno;
Insert into posassignment(DeviceID,EmpId,PlazaID,LaneID,AssignDateTime,AssignTranID,AssignedBy,LiveStatus,Start_at,End_at,IsNextDay,ClientId,Description)
values (DeviceID,EmpID,PlazaID,LaneID,AssignDateTime,AssignTranID,AssignedBy,'Y',Start_at,End_at,IsNextDay,ClientId,Description);
END
This is your code:
declare Sno int;
set Sno=(SELECT MAX(Sno) FROM posassignment WHERE DeviceID=DeviceID);
update posassignment set LiveStatus ='N' where Sno=Sno;
The update
is equivalent to:
update posassignment set LiveStatus = 'N' where Sno is not null;
That is, neither sno
reference is to the parameter. You have the same problem with DeviceID
:
CREATE DEFINER=`testmysql`@`%` PROCEDURE `PDADeviceAssign`(
IN in_DeviceID varchar(50),
IN in_EmpID varchar(50),
. . .
)
BEGIN
declare v_Sno int;
set v_Sno = (SELECT MAX(Sno)
FROM posassignment
WHERE DeviceID = in_DeviceID
);
update posassignment
set LiveStatus = 'N'
where Sno = v_Sno;