jeudi 30 juin 2016

MySQL Stored Procedure - IF EXISTS ... THEN returning unexpected result


The below is my Stored Procedure(Routine) to check whether or not a user with Username(input) exists in the database. Inside the database, I already have a user with Username - 'dev'. However, when I ran the below routine, it returned me with res = 1, which I expected it to be -1.

I called the routine this way. Please correct me too if I am calling it the wrong way. I am really new to MySQL Routines.

CALL usp_GetUserValidation ('dev', @ErrorCode)

Can any MySQL Routine pros here enlighten me on this? Thank you in advance guys :)

DELIMITER $$
CREATE PROCEDURE usp_GetUserValidation(IN `@Username` VARCHAR(255), OUT `@ErrorCode` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT 'To validate user login'
BEGIN

    IF EXISTS
    (SELECT UserID 
        FROM mt_User
        WHERE UserName = @Username)
    THEN
            SET @ErrorCode = -1;


    ELSE
        SET @ErrorCode =  1;

    END IF;


    SELECT @ErrorCode AS res;

END$$
DELIMITER ;

Aucun commentaire:

Enregistrer un commentaire