Having SQL Syntax error in MySQL stored procedure

0
votes

I am new to MySQL stored procedure. No clue why am I getting the following Error. Please help debug this.

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 this_user_id, this_user_fullname FROM user_master WHERE user_username = @th' at line 3

DELIMITER $$
CREATE PROCEDURE loginUser(IN this_user_name VARCHAR(100), IN this_user_password VARCHAR(100), OUT this_user_id BIGINT(11), OUT this_user_fullname VARCHAR(100))
BEGIN
  IF NOT EXISTS (SELECT user_id, user_full_name INTO this_user_id, this_user_fullname FROM user_master WHERE user_username = this_user_name AND user_password = this_user_password) THEN
    SET this_user_id = NULL;
    SET this_user_fullname = NULL;
  END IF;
END$$

My table structure is

Table Name: user_master

user_id bigint(11) No
user_full_name varchar(100) Yes NULL
user_username varchar(60) Yes NULL
user_password varchar(45) Yes NULL
user_security_question varchar(225) Yes NULL
user_security_answer varchar(255) Yes NULL
user_salt varchar(30) Yes NULL
user_status enum('ACTIVE', 'SUSPENDED', 'PENDING') Yes NULL
user_last_login_time timestamp Yes NULL
user_last_logout_time timestamp Yes NULL

IndexesDocumentation

PRIMARY BTREE Yes No user_id 4 A No
user_username_UNIQUE BTREE Yes No user_username 4 A Yes

1
mysqlsqlstored-procedures

1 Answers

0
votes

How about just doing this?

BEGIN
    -- Initialize the variables
    SET this_user_id = NULL;
    SET this_user_fullname = NULL;

    -- Set them
    SELECT user_id, user_full_name
    INTO this_user_id, this_user_fullname
    FROM user_master
    WHERE user_username = this_user_name AND user_password = this_user_password;

   . . .
END;