Why is mysql giving error while creating function?

Question

I'm trying to create a function that receives a code associated with a bus as a parameter and returns the number of stops the bus has in a route.

My code is the following:

create function num_paragem(bus_code int) 
returns int

Begin
    Declare x int default 0;
    select count(code_stop) into x from Bus_Stops where cod_bus = code;
end;

I keep getting these errors:

syntax error, unexpected END_OF_INPUT, expecting ';' -> it happens when I declare the variable x.

syntax error, unexpected END -> it occurs in the last line of the code.

Can someone please help me find the mistake on my function definition?

Thank you for your help.

Answer 1

You run into the delimiter problem: We need to tell the parser the delimiters inside the function as well as the delimiter of the function definition.

Try this;

-- set special delimiter
DELIMITER $$

create function num_paragem(bus_code int) 
returns int
Begin
    Declare x int default 0; -- delimiter inside the function
    select count(code_stop) into x from Bus_Stops where cod_bus = code;
end;

-- delimiter of the function definition
$$

-- set delimiter back to normal
DELIMITER ;