How to write nested if else if in MYSQL

Question

Serial sntax stitches should be correct. An error occurs while working on mysql

Error code: 1064. You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to '' on 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 $$ 

Answer 1

Update the saved 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 helps you ...!

Answer 2

I think maybe you should use CASE for this procedure.

Answer 3

 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