MariaDB function syntax error when creating a function

December 31, 2021

I’ve made this function but no matter what I add (delimiters) it’s still throwing the same error.

CREATE FUNCTION getNumberOfBuyers(IN userId INT) RETURNS INT
BEGIN
   DECLARE numberOfBuyers INT DEFAULT 0;
   SELECT COUNT(*) INTO numberOfBuyers FROM buyers WHERE id = userId;
   RETURN numberOfBuyers;
END;

The error is:

[42000][1064] You have an error in your SQL syntax; check the manual
that corresponds to your MariaDB server version for the right syntax
to use near ‘IN userId INT) RETURNS INT BEGIN DECLARE numberOfBuyers
INT DEFAULT 0; …’ at line 1

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.
Visit Medevel

>Solution :

There is no possibility to mark parameter as input parameter in mariadb. So remove the IN in the declaration:

CREATE FUNCTION getNumberOfBuyers(userId INT) RETURNS INT
BEGIN
   DECLARE numberOfBuyers INT DEFAULT 0;
   SELECT COUNT(*) INTO numberOfBuyers FROM buyers WHERE id = userId;
   RETURN numberOfBuyers;
END;

The general syntax declaration looks like:

CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]
RETURNS return_datatype

BEGIN

   declaration_section

   executable_section

END;