Error: subquery Returns more than one row

Question

Hi I have been trying to select more than one rows by calling the procedure through CallableStatement. While I am trying to populate the result set to the combo box the code returns the error as follows.

Java Error:

                   java.sql.SQLException: Subquery returns more than 1 row

Stored Procedure :

CREATE DEFINER=`user_name`@`%` PROCEDURE `GET_USER_PROFILE`(
            IN p_user_id VARCHAR(150),
            IN p_role VARCHAR(150),
            OUT p_user_data VARCHAR(200),
            OUT p_city VARCHAR(150),
            OUT p_state VARCHAR(150),
            OUT p_country VARCHAR(150),
            OUT q_Msg VARCHAR(150))
BEGIN

DECLARE available INT DEFAULT 0;
 SET p_city = (SELECT CITY FROM countries GROUP BY CITY);
 SET p_state = (SELECT STATE FROM countries GROUP BY STATE);
 SET p_country = (SELECT COUNTRY FROM countries GROUP BY COUNTRY);

SELECT COUNT(EMAIL) INTO available FROM STAFF_PROFILE WHERE EMAIL = p_user_id AND ROLE = p_role;

IF(available=1) THEN
        SET p_user_data = (SELECT * FROM STAFF_PROFILE WHERE EMAIL = p_user_id AND ROLE = p_role );
else
    SET q_Msg = 'USER_LOGGED_FIRST';
END IF;

END

Answer 1

@DaveHowes and @Ilya are correct, the issue is with your SQL statement.

Lets say in your Countries table consists of the following:

city            state           country
'New York'      'New York'      'USA'
'Los Angeles'   'California'    'USA'
'Chicago'       'Illinois'      'USA'
'Ottawa'        ''              'Canada'

Now, if we take your sub queries from your example:

SELECT city FROM countries GROUP BY city

would return:

city
'New York'
'Los Angeles'
'Chicago'
'Ottawa'

You're trying to assign a multiple results to a varchar hence you get the exception "Subquery returns more than 1 row".