How to write nested if else if in MYSQL

if-statement, mysql, nested-if

Solution

Update your stored function as follows

DELIMITER $$
DROP FUNCTION IF EXISTS `check2_login`$$
CREATE  FUNCTION `check2_login`(p_username VARCHAR(30),p_password VARCHAR(30),role VARCHAR(20)) RETURNS BOOL
    BEGIN
    DECLARE retval INT;
            IF role = "customer" THEN
                SELECT COUNT(custid) INTO retval FROM customer WHERE custid = p_username and pwd = p_password;
                IF retval != 0 THEN
                    RETURN TRUE;    
                ELSE
                    RETURN FALSE;                           
                END IF;
            ELSEIF role = "executive" THEN
                SELECT COUNT(execid) INTO retval FROM executive WHERE execid = p_username and pwd = p_password;
                IF retval != 0 THEN
                    RETURN TRUE;    
                ELSE
                    RETURN FALSE;                           
                END IF;
            ELSEIF role = "admin" THEN
                SELECT COUNT(empid) INTO retval FROM employee WHERE empid = p_username and pwd = p_password;
                IF retval != 0 THEN
                    RETURN TRUE;    
                ELSE
                    RETURN FALSE;                           
                END IF;
            ELSE
                RETURN FALSE;       
            END IF;
    END$$
DELIMITER ;

hope this will help you...!

Problem

The folowing sntax seams to be correct. While running on mysql gives error Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 27". ``` delimiter $$ create function check2_login(p_username varchar(30),p_password varchar(30),role varchar(20)) returns bool deterministic begin declare loginstatus bool default false; if role="customer"then select custid from customer where custid=p_username and pwd=p_password; if !row_count()=0 then select true into loginstatus; end if; else if role="executive"then select execid from executive where execid=p_username and pwd=p_password; if !row_count()=0 then select true into loginstatus; end if; else if role="admin"then select empid from employee where empid=p_username and pwd=p_password; if !row_count()=0 then select true into loginstatus; end if; else return loginstatus; end if; return loginstatus; end $$ ```

Original source