MySQL CREATE FUNCTION Syntax

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

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.

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;