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.

If you want to return a result set, create a stored procedure or a view instead of a function. — Sloan Thrasher
– Sloan Thrasher, Commented Apr 14, 2017 at 3:40
@SloanThrasher That's the problem. My professor wants it to be a stored function. — Thomas Yamakaitis
– Thomas Yamakaitis, Commented Apr 14, 2017 at 3:41
Then do not return a result set. You should have learned what a result set is and the difference between that and a value in class. HINT: Don't return names, but use a for next loop to store the column values into a VARCHAR(255). Same sort of thing for the error message. — Sloan Thrasher
– Sloan Thrasher, Commented Apr 14, 2017 at 3:46
Get used to that. If you want a career as a programmer, you will have to be able to teach yourself. I've been programming for 40 years, and spend about 1/3 of my time learning new stuff - and that's just to stay current. Never took a class, but have always run circles around those co-workers who have a degree, mainly because I can teach myself. (BTW, my degree is in a unrelated field - Nuclear Physics) — Sloan Thrasher
– Sloan Thrasher, Commented Apr 14, 2017 at 3:52
A quick search found 100's of pages on topic, and about 10 that were right on point with the assignment. — Sloan Thrasher
– Sloan Thrasher, Commented Apr 14, 2017 at 3:58

Community · Accepted Answer · 2017-05-23 12:09:52Z

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 ;

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related