Keep getting error despite having defined delimiter character

Question

I'm creating this procedure which should return a VARCHAR value as output. But I keep getting

ERROR 1064 (42000): 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 'BEGIN SELECT mgrType FROM Manager INTO mgType WHERE empNo=@eID; END

I have chosen // as new DELIMITER character so it is not an issue with delimiter. Here's my code.

DELIMITER //

CREATE PROCEDURE returnEmpTy( IN un VARCHAR(40), IN pw VARCHAR(60), OUT mgType VARCHAR(30) )
BEGIN 
    DECLARE eID INT;
    SELECT empNo INTO eID FROM Employee WHERE username=un AND pssWrd=pw;

    IF EXISTS(SELECT @eID FROM Manager)
        BEGIN
            SELECT mgrType FROM Manager INTO mgType WHERE empNo=@eID;
        END
            
END//

DELIMITER ;

edit: resolved!

Answer 1

There are several problems in your code, but none of them have anything to do with the delimiter.

One problem is that SELECT ... INTO allows the INTO right before FROM, or at the end of the query, but not where you used it before WHERE. Review the examples in the manual: https://dev.mysql.com/doc/refman/8.0/en/select-into.html

Another is that you use @eID for the variable eID. These are not the same variable in MySQL.

Another is that you use IF without THEN or END IF and you have no ; terminator anyway. You should refer the syntax and examples for MySQL's IF statement.

Another is that IF/THEN/END IF doesn't need a BEGIN block (unless you need to use DECLARE inside the block).

Your method of checking if the row exists in Manager is not going to do what you think. The EXISTS will return true if there are any rows in that table.

You don't need to check if there are rows in the table anyway. Just do the SELECT ... INTO. It'll assign NULL to mgType if there are no matching rows.

DELIMITER //

CREATE PROCEDURE returnEmpTy( IN un VARCHAR(40), IN pw VARCHAR(60), OUT mgType VARCHAR(30) )
BEGIN 
    DECLARE eID INT;
    SELECT empNo INTO eID FROM Employee WHERE username=un AND pssWrd=pw;

    SET mgType = NULL;
    SELECT mgrType FROM Manager WHERE empNo=eID INTO mgType;
            
END//

DELIMITER ;

You could simplify this further by using JOIN instead of a local variable:

DELIMITER //

CREATE PROCEDURE returnEmpTy( IN un VARCHAR(40), IN pw VARCHAR(60), OUT mgType VARCHAR(30) )
BEGIN 
    SELECT mgrType FROM Manager 
    JOIN Employee USING (empNo)
    WHERE username = un AND pssWrd = pw
    INTO mgType;
END//

DELIMITER ;