Syntax error when creating custom mysql function

Question

Trying to create a stored function but keep getting the same error.

I've tried a different body, changing integer to INT with/out (11)

DELIMITER $$

CREATE FUNCTION f_media (@dag INT, @week INT, @medium_naam varchar)
RETURNS integer

BEGIN 
    DECLARE result INT(11);

    SELECT result=COUNT(medium_name) FROM `TABLE 4` WHERE WEEK(date) = @week AND DAYOFWEEK(date) = dag AND medium_name == @medium_naam GROUP BY date;

    RETURN result;
END $$ 
DELIMITER ;

This is the exact error:

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

'@dag INT, @week INT, @medium_naam varchar)
RETURNS integer

BEGIN
DECLA' at line 1

Bill Karwin · Accepted Answer · 2019-06-06 18:27:12Z

2

You're putting the @ sigil in front of your function arguments. This is common if you're using Microsoft SQL Server, but MySQL has different syntax than Microsoft SQL Server.

@dag is called a user-defined variable. It has session scope.

dag is a local variable, either a function argument or one you create in a BEGIN/END block with DECLARE.

They are two different variables.

Because the variables don't have sigils, you have to be careful that you don't name a variable the same as one of the columns of the tables you query (you almost did so in the case of medium_naam). To resolve this ambiguity, I have the habit of adding a prefix of "in_" to the function arguments.

There is no == operator in SQL. Use = for comparison.

You shouldn't use GROUP BY if you intend your query will store a single result into a scalar variable.

Assigning the result to a variable in an expression can be done with := but not =. But you should avoid this usage because it may be removed from MySQL in a future version. I suggest you use INTO syntax instead.

Don't bother with the length in the INT(11) type. It means nothing.

Here's a corrected function:

CREATE FUNCTION f_media (in_dag INT, in_week INT, in_medium_naam varchar)
RETURNS INT

BEGIN 
    DECLARE result INT;

    SELECT COUNT(medium_name) INTO result
    FROM `TABLE 4` 
    WHERE WEEK(date) = in_week 
     AND DAYOFWEEK(date) = in_dag 
     AND medium_name = in_medium_naam;

    RETURN result;
END

edited Jun 6, 2019 at 18:27

answered Jun 6, 2019 at 15:22

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

2 Comments

Mahdi Safari Over a year ago

Hello Bill! I do the exact function syntax but I still have a syntax error in DECLARE result INT I don't know why. I using MySQL MariaDB and MySQL workbeanch thanks.

Bill Karwin Over a year ago

I can't guess what you did or what the exact error message is. I suggest you ask a new question and provide details. Also I don't use MariaDB and I consider it to be a different product from MySQL, because it forked ten years ago and it has been growing less compatible with MySQL since then.

Collectives™ on Stack Overflow

Syntax error when creating custom mysql function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related