MariaDB function syntax error when creating a function

Question

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

Jens · Accepted Answer · 2021-12-30 22:36:55Z

2

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;

answered Dec 30, 2021 at 22:36

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Gausmann Over a year ago

Since 10.8.0 MariaDB supports IN, OUT and IN OUT. However, I ran into the same problem because I was executing my code on a new and on an old MariaDB installation and it worked only on the new MariaDB installation.

Collectives™ on Stack Overflow

MariaDB function syntax error when creating a function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related