How to write nested if else if in MYSQL

Question

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 $$

Bet you wish you had a login table... – Tony Hopkinson Feb 10 '14 at 11:57 — Tony Hopkinson, Feb 10 '14 at 11:57

score 20 · Accepted Answer · answered Feb 10 '14 at 12:38

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...!

how to join two table in mysql function.bcoz here also designation table is also there. — user3239587, Feb 11 '14 at 07:04
post new question for your new requirement. if you update the same question then it will not visible to the users. so post new question for this... — Ashish Jagtap, Feb 11 '14 at 08:20
I would upvote your answer if you provided an explanation of what was the issue with the original code and why/how your solution fixes the problem — bikeman868, Oct 17 '16 at 01:15

score 8 · Answer 2 · answered Jun 22 '15 at 07:38

8

SELECT *, 
   IF(discount='0',
      ( IF(tax='0', discount_price, discount_price + (discount_price * (tax_rate/100))) ),
      ( IF(tax='0', price, price + (price * (tax_rate/100))) )
   ) AS price_last
FROM products WHERE id=1

answered Jun 22 '15 at 07:38

Limitless isa

3,689
36
28

score 5 · Answer 3 · answered Feb 10 '14 at 11:46

5

I think maybe you should use CASE statement for this procedure

answered Feb 10 '14 at 11:46

nes

1,046
1
7
10

How to write nested if else if in MYSQL

3 Answers3