abatie abatie - 3 months ago 5
MySQL Question

mysql function to return set data

I'm trying to get a stored procedure/function to return a set data type, but when I try, I get "You have an error in your SQL syntax ... near 'return ('one');". Any pointers? This is the first time I've tried playing with stored procedures... Thanks!

delimiter //
create function getset (set_type enum('a','b','c'))
returns set('one','two','three')
deterministic
begin
case set_type
when 'a'
return ('one');

when 'b'
return ('one,two');

when 'c'
return ('one,two,three');
end//
delimiter ;

Answer

The syntax of CASE is:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

You're missing all the THEN keywords and END CASE. It should be:

  case set_type
    when 'a'
        then return ('one');

    when 'b'
        then return ('one,two');

    when 'c'
        then return ('one,two,three');

  end case;

The error message clearly says that the problem is near the return keyword. It has nothing to do with the data type, it's a syntax error.