Create function that returns a "SELECT" statement result

Question

I was told to create a mysql function that, given the artwork title, return the name of the creator. I know it sounds silly, i could use just a select.

I followed some tutorials, but none of them helped me. When I try to create the function, mysql workbench says "Error Code: 1415. Not allowed to return a result set from a function"

Here is what i'm trying to do

delimiter $$
    create function findObject(workName VARCHAR(45)) returns VARCHAR(250)
    begin
    SELECT result= ar.artistName FROM artist as ar, art_object as oa
            where ar.artistCode= oa.artistCode
            and oa.title= workName 
            group by ar.artistName ;
    return result;
    end$$
delimiter ;

score 4 · Answer 1 · answered May 10 '19 at 00:22

That's not how you assign the result of a variable in a SELECT statement. You have to use SELECT ... INTO variable.

You also need to declare the variable first.

delimiter $$
create function findObject(workName VARCHAR(45)) returns VARCHAR(250)
begin
    DECLARE result VARCHAR(250);
    SELECT DISTINCT ar.artistName FROM artist as ar, art_object as oa
    INTO result
    where ar.artistCode= oa.artistCode
    and oa.title= workName;
    return result;
end$$
delimiter ;

SELECT result = ar.artistName is comparing the value of result with ar.artistName, and returning the result of that comparison from the SELECT statement.

Create function that returns a "SELECT" statement result

1 Answers1