CODEWITHSUNDEEP
mysqlmysql-error-1064
Ionas Skopelitis-Scholte
Ionas Skopelitis-Scholte

Reputation: 101

Syntax error when creating custom mysql function

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

Upvotes: 1

Views: 834

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562358

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

Upvotes: 2

Related Questions