abatie abatie - 1 year ago 177
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')
case set_type
when 'a'
return ('one');

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

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

Answer Source

The syntax of CASE is:

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

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download