Error Sql (1064) creating a function in MariaDB

Question

I am getting an error in MariaDB when trying to create a function. It gives me the following error and I leave the script below.

The script:

CREATE PROCEDURE `total_hh`(fechainicio  datetime, fechafin datetime) RETURNS int(11)
BEGIN
RETURN (select count(*) from datos d where d.fecha between date_format(fechainicio, "%Y,%m,%d %H,%i") and date_format(fechafin,"%Y,%m,%d %H,%i")
);
  END

ACTUALIZATION: Now im getting this error when i try to create the function. Else when i try to run the script like a query i have another error SQL (1558).

Answer 1

There are a couple of issues in your query:

• your SET statements do not end with a semicolon (main cause of your fired error)
• your function has no RETURN clause

Additionally, you should use the space reserved for input values with the variables you declare inside your function.

You can try using the following syntax:

CREATE FUNCTION total_hh(
    fechainicio DATETIME,
    fechafin    DATETIME
) 
RETURNS INT(11)
RETURN (SELECT COUNT(*) 
        FROM datos as d 
        WHERE d.fecha BETWEEN DATE_FORMAT(fechainicio, "%Y,%m,%d %H,%i") 
                          AND DATE_FORMAT(fechafin   , "%Y,%m,%d %H,%i"));

This compact form avoids you to change delimiters too.

Check the demo here.

Note: A function is defined by a header and a body. The workbench allows you to help with the definition of the header with the form. Inside the body section (cuerpo de la rutina) you need to add only the body (RETURN (SELECT ... );).