CODEWITHSUNDEEP
mysqlstored-procedures
Steve
Steve

Reputation: 3095

MySQL case when in stored procedure not working as expected

This is a stored procedure that has 3 possible cases. I can't figure out how to get the 3rd case to run. If I swap 2 and 3, 3 will run fine and 2 won't run, which leads me to believe it's not the actual code within each case.

CREATE DEFINER=`user`@`ip` PROCEDURE `api_userLogin_new`(IN _userType int,IN _clientId varchar(45),IN _username varchar(50))
    BEGIN
        CASE WHEN _userType = 1
           THEN
              *do stuff that works*
        WHEN '2'
           THEN 
              *do other stuff that works*
        WHEN '3'
           THEN 
              {this won't run, but does if I swap it out with 2}
        ELSE BEGIN END;
        END CASE;
    END;

It doesn't give an error, but it just doesn't do anything no matter which code is in the last of the 3 positions (i.e. move "WHEN '3'" and it's code up to where WHEN '2' is, and then 2 wont' run or error).

Upvotes: 0

Views: 382

Answers (3)

Sebastian Brosch
Sebastian Brosch

Reputation: 43584

You mixed the two possible CASE WHEN syntax descibed on the MySQL documentation. You can use the following solution using the variable only on the CASE:

CREATE DEFINER = `user`@`ip` PROCEDURE `api_userLogin_new`(IN _userType INT, IN _clientId VARCHAR(45), IN _username VARCHAR(50))
BEGIN
  CASE _userType
    WHEN 1 THEN
      -- do something
    WHEN 2 THEN 
      -- do something
    WHEN 3 THEN 
      -- do something
    ELSE
      BEGIN END;
  END CASE;
END;

... or the following solution using the variable on every WHEN (instead once on the CASE):

CREATE DEFINER = `user`@`ip` PROCEDURE `api_userLogin_new`(IN _userType INT, IN _clientId VARCHAR(45), IN _username VARCHAR(50))
BEGIN
  CASE
    WHEN _userType = 1 THEN
      -- do something
    WHEN _userType = 2 THEN 
      -- do something
    WHEN _userType = 3 THEN 
      -- do something
    ELSE 
      BEGIN END;
  END CASE;
END;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521804

I can only speak to code which I can see, not that I can't, but I see a few potential problems with your code. First, every predicate of the CASE expression should end with a delimiter. Also, if you don't have an ELSE condition, then don't include one. So I recommend this version:

CREATE DEFINER=`user`@`ip` PROCEDURE `api_userLogin_new`(IN _userType int,IN _clientId varchar(45),IN _username varchar(50))
    BEGIN
        CASE _userType WHEN 1
             THEN *do stuff that works*;
             WHEN 2
             THEN *do other stuff that works*;
             WHEN 3
             THEN {this won't run, but does if I swap it out with 2};
        END CASE;
    END;

Upvotes: 1

dustytrash
dustytrash

Reputation: 1590

Use the full expression:

CREATE DEFINER=`user`@`ip` PROCEDURE `api_userLogin_new`(IN _userType int,IN _clientId varchar(45),IN _username varchar(50))
    BEGIN
        CASE WHEN _userType = 1
           THEN
              *do stuff that works*
        WHEN _userType = '2'
           THEN 
              *do other stuff that works*
        WHEN _userType  = '3'
           THEN 
              *do other stuff that works*
        ELSE BEGIN END;
        END CASE;
    END;

Side note: Your scenario was occurring since '2' and '3' evaluate to TRUE in MYSQL.

Upvotes: 1

Related Questions