MySQL Error - "Not allowed to return a result set from a function"

Question

This is for my database class. My Professor wants us to make a stored function. Here is the question specifically.

Please write a stored function named fHW2_4_XXXX (...) based on the Guest table. Your program should take one argument city and it should meet the following 3 test cases.

Full Question ~ Given by my Professor ~ Click here to see it!

I realize there are similar questions, but the ones I've seen have not helped. I'm trying to create a stored function in MySQL, and it's giving me the error: "Not allowed to return a result set from a function."

DELIMITER //

CREATE FUNCTION fHW2_4_xxxx(city_name VARCHAR(25))
RETURNS VARCHAR(255)

BEGIN
    DECLARE names VARCHAR(255);
    IF (city_name = '' OR city_name IS NULL) THEN
        SELECT 'Please input a valid city.' AS message;
    ELSE
        SELECT GROUP_CONCAT(guestname SEPARATOR ',') INTO names FROM dreamhome.Guest WHERE guestaddress LIKE CONCAT('%', city_name, '%');
        RETURN names;
    END IF;
END //

DELIMITER ;

The goal is to select all guestname(s) from dreamhome.Guest whose address contains the given (input) city name, but it has to return the values as "John Kay, Mike Ritchie". This is for my Database class. I understand it has something to do with my SELECT statements, but I see no other reliable way to do it.

Answer 1

Thanks to @SloanThrasher, I have figured it out!

So, I found the best way to fix this was to declare a cursor, and run through the search results.

Here is my code:

--Function fHW2_4_xxxx--
DELIMITER //

DROP FUNCTION IF EXISTS fHW2_4_xxxx //

CREATE FUNCTION fHW2_4_yamakait(city_name VARCHAR(25))
RETURNS VARCHAR(2000)

BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_name varchar(100) DEFAULT "";
DECLARE name_list varchar(2000) DEFAULT "";

-- declare cursor for guest name
DEClARE name_cursor CURSOR FOR 
     SELECT guestname FROM dreamhome.Guest WHERE guestaddress LIKE CONCAT('%', city_name, '%');

-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET v_finished = 1;

IF (city_name != '' AND city_name IS NOT NULL) THEN  
    OPEN name_cursor;
    get_name: LOOP 
        FETCH name_cursor INTO v_name;

        IF (v_finished = 1 AND name_list = "") THEN
            RETURN 'No result found.';
            LEAVE get_name;
        END IF;     
        IF v_finished = 1 THEN
            LEAVE get_name;
        END IF;

        -- build name list
        IF (name_list = "") THEN
            SET name_list = v_name;
        ELSE
            SET name_list = CONCAT(name_list, "," , v_name);
        END IF;
    END LOOP get_name;

    RETURN name_list;

    CLOSE name_cursor;
ELSE
    RETURN 'Please input a valid city.';
END IF;

END //

DELIMITER ;