Update doesn't work in stored procedure

Question

I have a stored procedure to update a column(

LiveStatus

) to 'N' before inserting a new row of data and setting its column(

LiveStatus

) to 'Y' if the

DeviceID

of the new values is already there and its LiveSTatus is 'Y'.

It inserts the new values but the

livestatus

of the previous row with the same

DeviceID

is still 'Y'.

The stored procedure:

    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

Answer 1

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;

Update doesn't work in stored procedure

Sign up