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.

enter image description here

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).

enter image description here

• Akina: You create your function using some third-party client software. Read its help about "What is correct way to create stored objects".
• Eduardo Estay Atenas: Ok, sorry im new in Stack.

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 ... );).

• Eduardo Estay Atenas: Hi lemon and thanks for your response. Now im getting this, ERROR DE SQL (1303): Cant create a function from within another stored routine. This if i put it like Function do not return a result. However, if i put function thats returns a result (int) i recibe ERROR SQL (1303) at line 8.
• lemon: This description of the error tells you what the issue is: "Cant create a function from within another stored routine", you should do it outside of any routine.
• lemon: I've added a demo at the bottom of the answer for you to see how it is expected to work. @EduardoEstayAtenas
• Eduardo Estay Atenas: I am very grateful for your kindness Lemon. Sorry to bother you so much, but now I get SQL error (1558): Column count of mysql.proc is wrong (expected 21, found 20). I followed your steps creating the data table first and then when creating the function it throws me this error. I am really very frustrated.
• lemon: Check the note on the updated answer.