Routine using "Select INTO" with MySQL v 5.5 gives 1064 syntax error

Question

What am I doing wrong here? I keep getting:

#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 'INTO userID, 
currentStatus, failedLogin FROM access WHERE username = p_UserName ' at line 9 

And of course, here is my routine:

DELIMITER $$

DROP PROCEDURE IF EXISTS `user_login`$$

CREATE PROCEDURE `user_login`(IN v_UserName VARCHAR(45), IN v_Password VARCHAR(45), IN v_IP VARCHAR(45), IN v_Source VARCHAR(45))

BEGIN

    DECLARE userID INT(10);
    DECLARE currentStatus VARCHAR(10);
    DECLARE failedLogin INT(4);

    IF EXISTS(SELECT access_user_id, status, failed_login_count INTO userID, currentStatus, failedLogin FROM access WHERE username = v_UserName AND hash = v_Password LIMIT 1) THEN
        UPDATE access SET failed_login_count=0 WHERE access_user_id=userID;
        INSERT INTO last_login (profiles_user_id, timestamp, ip_address, login_source) VALUES (userID, NOW(), v_IP, v_Source) ON DUPLICATE KEY UPDATE timestamp=VALUES(timestamp), ip_address=VALUES(ip_address), login_source=VALUES(login_source);
        INSERT INTO login_history (profiles_user_id, timestamp, ip_address, login_source) VALUES (userID, NOW(), v_IP, v_Source);

        CASE currentStatus
            WHEN 'Active' THEN
                SELECT userID, currentStatus;
            WHEN 'Dormant' THEN
                UPDATE access SET status='Active' WHERE access_user_id=userID;
                SELECT userID, currentStatus;               
            WHEN 'Locked' THEN
                SELECT userID, currentStatus;
            ELSE

        END CASE;

    ELSE
        IF EXISTS(SELECT access_user_id, failed_login_count INTO userID, failedLogin FROM access WHERE username = p_UserName LIMIT 1) THEN
            failedLogin = failedLogin +1;
            UPDATE access SET failed_login_count=failedLogin WHERE access_user_id=userID;

            CASE failedLogin
                WHEN (<10) THEN
                    SELECT 0, 'Invalid';
                WHEN (>10) THEN
                    UPDATE access SET status='Locked' WHERE access_user_id=userID;
                    SELECT 0, 'Invalid';
                ELSE
                    UPDATE access Set failed_login_count=1 WHERE access_user_id=userID;
                    SELECT 0, 'Invalid';
            END CASE;

        ELSE
            SELECT 0, 'Invalid';
        END IF;
    END IF;
END$$

DELIMITER ;

I've dug through the MySQL 5.5 documentation and according to it, my SELECT INTO syntax is correct... Well, obviously not according to the server... Anyone have any ideas?

Answer 1

There are some problems:

Using SELECT...INTO statement inside EXISTS clause. You cannot do it because SELECT...INTO does not return result-set, it just sets variables. Workaround (split the query and check ID value in the IF statement):

SELECT access_user_id, status, failed_login_count INTO userID, currentStatus, failedLogin FROM access WHERE username = p_UserName AND hash = p_Password LIMIT 1;

IF userID IS NOT NULL THEN
...

Remove ELSE keyword from the first CASE struct.

Last CASE struct has wrong syntax, try this one -

CASE
  WHEN failedLogin < 10 THEN
    SELECT 0, 'Invalid';
  WHEN failedLogin > 10 THEN
    UPDATE access SET status='Locked' WHERE access_user_id=userID;
    SELECT 0, 'Invalid';
  ELSE
    UPDATE access Set failed_login_count=1 WHERE access_user_id=userID;
    SELECT 0, 'Invalid';
END CASE;