Cannot set variable in if else statement in MySql

Question

I created a stored procedure which requirement as below

When I execute the stored procedure with one username typed in which be compare if username are exists in database then (variable) result_userId will set at userId ELSE if username dose not exists in database then (variable) result_userId will be set the number 99

BUT I CANNOT DO THAT

Please help me !

::CODE BELOW::

DELIMITER $$
USE `shoppy` $$
CREATE PROCEDURE `testProc02`
(
    IN  _username  CHAR(50),
    OUT result_userId   INT UNSIGNED
)
BEGIN
    SELECT @uId := userId FROM user 
    WHERE userName = _username;
    IF @uId = NULL THEN
        SET result_userId = 99;
    ELSE
        SET result_userId = @uId;
    END IF;    
END $$
DELIMITER ;

When I CALL testProc02();

enter image description here

Darwin von Corax · Accepted Answer · 2016-04-23 06:44:22Z

3

You can't use the equality operator = on null. You have to test the expression IS NULL or use the null-safe equality operator <=>.

Your code should be

DELIMITER $$
USE `shoppy` $$
CREATE PROCEDURE `testProc02`
(
    IN  _username  CHAR(50),
    OUT result_userId   INT UNSIGNED
)
BEGIN
    SELECT @uId := userId FROM user 
    WHERE userName = _username;
    IF @uId IS NULL THEN
        SET result_userId = 99;
    ELSE
        SET result_userId = @uId;
    END IF;    
END $$
DELIMITER ;

What happens is that @uID = NULL always evaluates to null, which the if interprets as false.

answered Apr 23, 2016 at 6:44

Darwin von Corax

5,2663 gold badges20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kiên Trần Over a year ago

Thanks a lot, because my WorkBench cannot refesh the result_userId so i verry hard for testing this codes i dont know why my Workbench is only show the latest result number T_T whether i try to do hard tried

Collectives™ on Stack Overflow

Cannot set variable in if else statement in MySql

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related