3

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

2
  • You create your function using some third-party client software. Read its help about "What is correct way to create stored objects".
    – Akina
    Jan 17, 2023 at 18:25
  • Ok, sorry im new in Stack. Jan 17, 2023 at 20:27

1 Answer 1

4

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

5
  • 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. Jan 17, 2023 at 18:13
  • 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
    Jan 17, 2023 at 18:14
  • I've added a demo at the bottom of the answer for you to see how it is expected to work. @EduardoEstayAtenas
    – lemon
    Jan 17, 2023 at 18:20
  • 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. Jan 17, 2023 at 19:42
  • Check the note on the updated answer.
    – lemon
    Jan 17, 2023 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.