Creating stored functions in MySQL - Visual Walkthrough

Concept Flow - Creating stored functions

Start

↓

Define function header

↓

Write function body

↓

Declare variables if needed

↓

Use RETURN to send back result

↓

End function definition

↓

Function ready to use

↓

Call function in queries

↓

Get result from function

You start by defining the function name and parameters, write the body with logic, use RETURN to output a value, then save it. After that, you can call the function in your SQL queries.

Execution Sample

MySQL

DELIMITER $$
CREATE FUNCTION add_two_numbers(a INT, b INT) RETURNS INT
BEGIN
  RETURN a + b;
END$$
DELIMITER ;

This code creates a stored function named add_two_numbers that takes two numbers and returns their sum.

Execution Table

Step	Action	Details	Result
1	Set DELIMITER	Change delimiter to $$ to define function	DELIMITER set to $$
2	Create FUNCTION	Define function add_two_numbers with parameters a and b	Function header created
3	Begin block	Start function body with BEGIN	Function body started
4	RETURN statement	Return sum of a and b	Returns a + b
5	End block	Close function body with END	Function body ended
6	Reset DELIMITER	Change delimiter back to ;	DELIMITER reset to ;
7	Call function	Execute SELECT add_two_numbers(3, 5);	Returns 8
8	Call function	Execute SELECT add_two_numbers(10, -2);	Returns 8
9	Exit	Function created and tested successfully	Execution complete

💡 Function created and tested; calls return expected sums.

Variable Tracker

Variable	Start	Call 1 (a=3,b=5)	Call 2 (a=10,b=-2)
a	undefined	3	10
b	undefined	5	-2
RETURN value	undefined	8	8

Key Moments - 3 Insights

Why do we change the DELIMITER before and after creating the function?

We change DELIMITER to $$ so MySQL knows the function definition ends at END$$, not at the usual ; which appears inside the function. After defining, we reset DELIMITER to ; as shown in execution_table rows 1 and 6.

What happens if we forget the RETURN statement inside the function?

Without RETURN, the function does not send back a value, causing errors or NULL results. The RETURN in row 4 is essential to output the sum.

Can we call the function before it is created?

No, the function must be created first (rows 2-6). Calling it before creation will cause an error, as shown in row 7 where the function is successfully called.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of calling add_two_numbers(3, 5)?

A 35

B 15

C 8

D Error

💡 Hint

Check row 7 in the execution_table where the function is called with (3,5).

At which step do we reset the DELIMITER back to semicolon?

A Step 2

B Step 6

C Step 4

D Step 1

💡 Hint

Look at the execution_table row describing resetting DELIMITER.

If we remove the RETURN statement, what will happen when calling the function?

A It will return NULL or cause an error

B It will return the sum anyway

C It will return zero

D It will return the first parameter

💡 Hint

Refer to key_moments about the importance of RETURN statement.

Concept Snapshot

CREATE FUNCTION name(params) RETURNS type
BEGIN
  -- function body
  RETURN value;
END;

Use DELIMITER to define functions with multiple statements.
RETURN sends back the result.
Call function in queries like SELECT name(args);

Full Transcript

Creating stored functions in MySQL involves defining a function with a name and parameters, writing the logic inside a BEGIN...END block, and using RETURN to output a value. We change the DELIMITER temporarily to allow semicolons inside the function body without ending the definition early. After creating the function, we reset the DELIMITER and can call the function in SQL queries to get results. The RETURN statement is essential to send back the computed value. This process helps reuse logic easily in queries.