CODEWITHSUNDEEP
mysqlvariablesstored-proceduresparameter-passing
Sebastian
Sebastian

Reputation: 133

How to compare an argument value input with a variable in stored procedure mysql?

I'm trying to compare a variable declared within the procedure body with an input argument , but the result is always false.

Here es my code:

create procedure UserLogin(email VARCHAR(64),
                           reg_id VARCHAR(355),
                           code_version VARCHAR(10))

        SELECT REG_ID INTO v_mi_regId
        from USUARIO
        where ID_USUARIO = email;

        IF reg_id != v_mi_regId THEN <-- always false

        UPDATE USUARIO 
        SET REG_ID = reg_id
        WHERE ID_USUARIO = email;

        END IF;
END

In the USUARIO table, the value REG_ID of a specific user is different from the argument. The update operation is never performed

Upvotes: 1

Views: 3752

Answers (1)

wchiquito
wchiquito

Reputation: 16559

Avoid naming variables and parameter as columns of your tables.

Try:

/* CODE FOR DEMONSTRATION PURPOSES */

DELIMITER //

DROP PROCEDURE IF EXISTS `UserLogin`//

CREATE PROCEDURE `UserLogin`(
  `p_email` VARCHAR(64),
  `p_reg_id` VARCHAR(355),
  `code_version` VARCHAR(10)
)
BEGIN
   DECLARE `v _mi_regId` VARCHAR(355);

   SELECT `REG_ID` INTO `v _mi_regId`
   FROM `USUARIO`
   WHERE `ID_USUARIO` = `p_email`;

   IF `p_reg_id` != `v_mi_regId` THEN
      UPDATE `USUARIO` 
      SET `REG_ID` = `p_reg_id`
      WHERE `ID_USUARIO` = `p_email`;
   END IF;
END//

DELIMITER ;

/* CODE FOR DEMONSTRATION PURPOSES */

SQL Fiddle demo

See:

Upvotes: 2

Related Questions