Balsher Singh Balsher Singh - 2 months ago 9
MySQL Question

What is wrong this simple function?

Here is a simple function I am creating for purpose of practice. But receive the given error.

DELIMITER $$

CREATE FUNCTION Weighted_Average(n1 INT, n2 INT, n3 INT, n4 INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE avg INT;
SET avg = (n1 + n2 + n3*2 + n4*4)/8;
RETURN avg;
END;
DELIMITER $$


Error Code: 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 'n4*4)/8;   RETURN avg;  END' at line 6

I am comparing it to this post:

MySQL CREATE FUNCTION Syntax

Someone with experience can probably point out my mistake while I am getting no where. I don't see what is wrong where the error is asking me to look. Note: I am using workbench.

Answer

Are you sure you don't mean the following, mainly the bottom of it?

DROP FUNCTION IF EXISTS Weighted_Average;
DELIMITER $$
CREATE FUNCTION Weighted_Average(n1 INT, n2 INT, n3 INT, n4 INT)
RETURNS INT
 DETERMINISTIC
 BEGIN
  DECLARE avg INT;
  SET avg = (n1 + n2 + n3*2 + n4*4)/8;
  RETURN avg;
 END;$$
 DELIMITER ;

A DELIMITER is necessary for some client-side environments to delineate the beginning and end of blocks, and to change the end-of-lines for statements.

As the DELIMITER is defaulted to ; out of the box, we know how to end a sql line with it.

When it comes to specialty blocks like CREATE PROCEDURE , FUNCTION, EVENT, TRIGGER, there needs to be some protocol for the client and server to know where the whole thing ends.

So, for clients like MySQL Workbench and similar, we use DELIMITER blocks. We change it to something funny up top, code as usual, and do the ending as seen above. Setting the DELIMITER back to our normal ;

They are not needed for PHPMyAdmin. And presumably not so for SqlFiddle

Comments