Creating User-Defined Functions (CREATE FUNCTION)0%

Deterministic vs Non-Deterministic Functions

Creating User-Defined Functions (CREATE FUNCTION)

Advanced14 min read•Updated: 2026-09-12

Creating User-Defined Functions (CREATE FUNCTION)

While Stored Procedures are designed to perform procedural tasks and return full result sets, Stored Functions (User-Defined Functions) are designed to compute and return a single scalar value.

Because functions return a scalar value, they can be invoked directly inside standard SQL expressions: in SELECT lists, WHERE filters, ORDER BY clauses, and HAVING checks.

Stored Function Syntax

1DELIMITER //
2 
3CREATE FUNCTION function_name(param1 datatype, param2 datatype)
4RETURNS return_datatype
5[DETERMINISTIC | NOT DETERMINISTIC]
6[READS SQL DATA | NO SQL | CONTAINS SQL]
7BEGIN
8    DECLARE result return_datatype;
9    -- Logic here
10    RETURN result;
11END //
12 
13DELIMITER ;

Practical Example: Tax Calculation Function

Let's build a reusable financial function to calculate tiered sales tax based on product categories:

1DELIMITER //
2 
3CREATE FUNCTION CalculateItemTax(
4    p_price DECIMAL(10, 2),
5    p_category VARCHAR(50)
6) 
7RETURNS DECIMAL(10, 2)
8DETERMINISTIC
9NO SQL
10BEGIN
11    DECLARE tax_rate DECIMAL(4, 2);
12 
13    IF p_category = 'Groceries' THEN
14        SET tax_rate = 0.00; -- Tax-exempt
15    ELSEIF p_category = 'Clothing' THEN
16        SET tax_rate = 0.04; -- 4% tax
17    ELSEIF p_category = 'Electronics' THEN
18        SET tax_rate = 0.08; -- 8% tax
19    ELSE
20        SET tax_rate = 0.05; -- Standard 5%
21    END IF;
22 
23    RETURN ROUND(p_price * tax_rate, 2);
24END //
25 
26DELIMITER ;

Calling Stored Functions Inline in Queries

Unlike procedures which require CALL, functions are embedded directly into SQL statements:

1-- Invoking function inside a SELECT statement
2SELECT 
3    product_name,
4    category,
5    retail_price,
6    CalculateItemTax(retail_price, category) AS sales_tax,
7    (retail_price + CalculateItemTax(retail_price, category)) AS final_checkout_price
8FROM products;
9 
10-- Invoking function inside a WHERE clause
11SELECT product_name, retail_price 
12FROM products 
13WHERE CalculateItemTax(retail_price, category) > 20.00;

Security Requirement: log_bin_trust_function_creators

If binary logging is enabled on your MySQL server, creating functions requires declaring their deterministic nature. If you encounter: ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

You can resolve this either by declaring DETERMINISTIC or toggling:

1SET GLOBAL log_bin_trust_function_creators = 1;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

How does a Stored Function fundamentally differ from a Stored Procedure in MySQL?

Question 2

Which keyword specifies the return data type in a stored function signature?

Question 3

Which statement is used inside the function body to output the calculated result?

Question 4

How is a stored function called in SQL?

Question 5

Why does MySQL require function authors to specify DETERMINISTIC or READS SQL DATA when binary logging is active?

Next Lesson

Deterministic vs Non-Deterministic Functions

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Error Handling & Handlers in Stored Programs	Deterministic vs Non-Deterministic Functions

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Deterministic vs Non-Deterministic Functions

Creating User-Defined Functions (CREATE FUNCTION)

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Creating User-Defined Functions (CREATE FUNCTION)

Because functions return a scalar value, they can be invoked directly inside standard SQL expressions: in SELECT lists, WHERE filters, ORDER BY clauses, and HAVING checks.

Stored Function Syntax

1DELIMITER //
2 
3CREATE FUNCTION function_name(param1 datatype, param2 datatype)
4RETURNS return_datatype
5[DETERMINISTIC | NOT DETERMINISTIC]
6[READS SQL DATA | NO SQL | CONTAINS SQL]
7BEGIN
8    DECLARE result return_datatype;
9    -- Logic here
10    RETURN result;
11END //
12 
13DELIMITER ;

Practical Example: Tax Calculation Function

Let's build a reusable financial function to calculate tiered sales tax based on product categories:

1DELIMITER //
2 
3CREATE FUNCTION CalculateItemTax(
4    p_price DECIMAL(10, 2),
5    p_category VARCHAR(50)
6) 
7RETURNS DECIMAL(10, 2)
8DETERMINISTIC
9NO SQL
10BEGIN
11    DECLARE tax_rate DECIMAL(4, 2);
12 
13    IF p_category = 'Groceries' THEN
14        SET tax_rate = 0.00; -- Tax-exempt
15    ELSEIF p_category = 'Clothing' THEN
16        SET tax_rate = 0.04; -- 4% tax
17    ELSEIF p_category = 'Electronics' THEN
18        SET tax_rate = 0.08; -- 8% tax
19    ELSE
20        SET tax_rate = 0.05; -- Standard 5%
21    END IF;
22 
23    RETURN ROUND(p_price * tax_rate, 2);
24END //
25 
26DELIMITER ;

Calling Stored Functions Inline in Queries

Unlike procedures which require CALL, functions are embedded directly into SQL statements:

1-- Invoking function inside a SELECT statement
2SELECT 
3    product_name,
4    category,
5    retail_price,
6    CalculateItemTax(retail_price, category) AS sales_tax,
7    (retail_price + CalculateItemTax(retail_price, category)) AS final_checkout_price
8FROM products;
9 
10-- Invoking function inside a WHERE clause
11SELECT product_name, retail_price 
12FROM products 
13WHERE CalculateItemTax(retail_price, category) > 20.00;

Security Requirement: log_bin_trust_function_creators

You can resolve this either by declaring DETERMINISTIC or toggling:

1SET GLOBAL log_bin_trust_function_creators = 1;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

How does a Stored Function fundamentally differ from a Stored Procedure in MySQL?

Question 2

Which keyword specifies the return data type in a stored function signature?

Question 3

Which statement is used inside the function body to output the calculated result?

Question 4

How is a stored function called in SQL?

Question 5

Why does MySQL require function authors to specify DETERMINISTIC or READS SQL DATA when binary logging is active?

Next Lesson

Deterministic vs Non-Deterministic Functions

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Error Handling & Handlers in Stored Programs	Deterministic vs Non-Deterministic Functions

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next