CODEWITHSUNDEEP
mysql
abatie
abatie

Reputation: 169

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 ;

Upvotes: 0

Views: 172

Answers (1)

Barmar
Barmar

Reputation: 780798

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.

Upvotes: 1

Related Questions