CODEWITHSUNDEEP
mysqlvariablesstored-procedures
Donnie Rock
Donnie Rock

Reputation: 525

Stored procedure in MySQL with variable on the conditional

I'm doing a table where I store the last time the users do login/logout. Just one row storing an Id, the action as a bit field and the moment as a DATETIME. My idea is to do a stored procedure to make an insert when the user is new and an update when the user exists. I've done this code:

    DELIMITER $$
CREATE PROCEDURE sp_LastAction(in id_in int, accion_in bit)
begin
SELECT @CONT:= IdUsuario FROM login WHERE IdUsuario = id_in;
IF NOT @CONT then
INSERT INTO login(IdUsuario, Fecha, Accion)
values (id_in, NOW(), accion_in);
ELSE
UPDATE login SET Fecha = NOW(), Accion = accion_in
WHERE IdUsuario = id_in LIMIT 1;
end IF;
end$$
DELIMITER ;

But when I call the procedure it doesn't do anything, just returns the variable as a void field.

Upvotes: 2

Views: 213

Answers (1)

wchiquito
wchiquito

Reputation: 16551

You can make a UPSERT.

CREATE PROCEDURE `sp_LastAction`(id_in int, accion_in bit)
BEGIN
  INSERT INTO `login` (`idusuario`, `fecha`, `accion`) VALUES (id_in, now(), accion_in)
    ON DUPLICATE KEY UPDATE `fecha` = now(), `accion` = accion_in;
END //

Here's an example: SQL Fiddle

Upvotes: 4

Related Questions