Syntax error in mysql, using create funtion

Question

create funtion tra (g_name varchar(10)) 
returns int 
begin   
  declare gpa int;   
  set gpa = 0;   
  gpa = case  
  when g_name = 'A+' THEN 5  
  when g_name = 'B+' THEN 4   ELSE 3
  END CASE;   
  RETURN gpa; end;//

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'funtion tra (g_name varchar(10)) returns int begin declare gpa int; set gpa ' at line 1

what is error in declare?

GMB GMB · Accepted Answer · 2020-10-03T17:40:19

Apart from the obvious typo on funtion, you are not using the case statement correctly. As opposed to the case expression, that is typically used within a query and returns a value, the case statement is a flow-control structure. Each branch should contain a statement, or a list of statement.

The correct syntax would be:

create function tra (g_name varchar(10)) 
returns int 
begin   
    declare gpa int;  

    case  
      when g_name = 'a+' then set gpa = 5;  
      when g_name = 'b+' then set gpa = 4;   
      else set gpa = 3;
    end case;   
    
    return gpa; 
end;
//

Syntax error in mysql, using create funtion

1 Answers