MySQL CREATE FUNCTION Syntax

Question

I am trying to create a function in MySQL:

Here is the SQL code:

CREATE FUNCTION F_Dist3D (x1 decimal, y1 decimal) 
RETURNS decimal
DETERMINISTIC
BEGIN 
  DECLARE dist decimal;
  SET dist = SQRT(x1 - y1);
  RETURN dist;
END;

I am getting the following error:

#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 '' at line 10

I am running this create statement in phpMyAdmin. What is wrong with this function?

Answer 1

You have to override your ; delimiter with something like $$ to avoid this kind of error.

After your function definition, you can set the delimiter back to ;.

This should work:

DELIMITER $$
CREATE FUNCTION F_Dist3D (x1 decimal, y1 decimal) 
RETURNS decimal
DETERMINISTIC
BEGIN 
  DECLARE dist decimal;
  SET dist = SQRT(x1 - y1);
  RETURN dist;
END$$
DELIMITER ;

Answer 2

MySQL create function syntax:

DELIMITER //

CREATE FUNCTION GETFULLNAME(fname CHAR(250),lname CHAR(250))
    RETURNS CHAR(250)
    BEGIN
        DECLARE fullname CHAR(250);
        SET fullname=CONCAT(fname,' ',lname);
        RETURN fullname;
    END //

DELIMITER ;

Use This Function In Your Query

SELECT a.*,GETFULLNAME(a.fname,a.lname) FROM namedbtbl as a


SELECT GETFULLNAME("Biswarup","Adhikari") as myname;

Watch this Video how to create mysql function and how to use in your query

Create Mysql Function Video Tutorial

Answer 3

Adding to the accepted answer, for the sake of completeness, here's how to drop and call the function, along with a simplification to remove the intermediate variable (although OP probably has more work to do in the function and is just trying to get the syntax working):

DROP FUNCTION IF EXISTS F_Dist3D;

DELIMITER $$
CREATE FUNCTION F_Dist3D (x1 decimal, y1 decimal) 
RETURNS decimal
DETERMINISTIC
BEGIN 
  RETURN SQRT(x1 - y1);
END$$
DELIMITER ;

SELECT F_Dist3D(11, 2); -- => 3

Note that the assumption here is that x1 is greater than y1, since SQRT expects a positive number.

Answer 4

A function:

• can have zero or more SQL statements with BEGIN ... END statement. *No statement without BEGIN ... END statement gets error.

• can have only single RETURN statement not changing the delimiter to $$ without error.

• can get zero or more values with zero or more parameters from the caller then, return a value to the caller.

• must return a value with RETURN statement.

• must be DETERMINISTIC or NOT DETERMINISTIC. *If any one of them is not set, NOT DETERMINISTIC is set implicitly.

• can have SELECT INTO statement but cannot have SELECT without INTO otherwise there is the error.

• can have local variables and user-defined variables. *My answer explains local variables and user-defined variables.

• cannot have transaction otherwise there is the error.

• is atomic by default so if there is error, it is rollbacked automatically. *My answer explains it in detail.

*The doc explains a function in detail.

For example, you create test table as shown below:

CREATE TABLE test (
  num int
);

Then, you insert the row whose num is 2 as shown below:

INSERT INTO test (num) VALUES (2);

Now, you can create addition(value INT) function which adds value to num and returns value to the caller as shown below. *Without DETERMINISTIC gets the error and my answer explains DETERMINISTIC and you must set RETURN statement and RETURNS <type> clause to a function otherwise you get the error and the error respectively and in a function, SELECT INTO is allowed to use but SELECT without INTO is not allowed to use because there is the error basically, you need to change the default delimiter ; to something like $$ when creating a function otherwise there is error, then after creating a function, you need to change the delimiter $$ back to ; as shown below and my answer explains delimiter and you must select a database when creating a function otherwise there is the error:

DELIMITER $$

CREATE FUNCTION addition(value INT) RETURNS INT 
DETERMINISTIC
BEGIN
UPDATE test SET num = num + value;
SELECT num INTO value FROM test;
RETURN value;
END$$ 

DELIMITER ;

Then, you can call addition(3) with SELECT statement, then 5 is returned and 3 is added to num as shown below:

mysql> SELECT addition(3);
+-------------+
| addition(3) |
+-------------+
|           5 |
+-------------+
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|  5   |
+------+

In addition, you can create addition() function with the user-defined variable e.g. @my_value and without parameters as shown below:

DELIMITER $$

CREATE FUNCTION addition() RETURNS INT 
DETERMINISTIC
BEGIN                       -- ↓ Here
UPDATE test SET num = num + @my_value;
SELECT num INTO @my_value FROM test;
RETURN NULL;    -- ↑ Here
END$$ 

DELIMITER ;

Then, you set @my_value with 3 and call addition(), then 3 is added to num and @my_value has num value 5 as shown below:

mysql> SET @my_value = 3;
...
mysql> SELECT addition();
+------------+
| addition() |
+------------+
|       NULL |
+------------+
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|  5   |
+------+
...
mysql> SELECT @my_value;   
+-----------+
| @my_value |
+-----------+
|         5 |
+-----------+

And, in this case below, you can create addition(value INT) function not changing the delimiter to $$ without error:

CREATE FUNCTION addition(value INT) RETURNS INT
DETERMINISTIC
RETURN value;