Resolving MySQL Error 1320: Ensuring Functions Conclude with a RETURN Statement

MySQL Error 1320 – SQLSTATE: 2F005 (ER_SP_NORETURNEND) is a common error encountered when a stored function does not conclude with a RETURN statement. The error message “FUNCTION %s ended without RETURN” indicates that the function is missing a RETURN statement, which is mandatory for all stored functions in MySQL, as they must return a value.

Understanding the Error

In MySQL, a stored function is expected to return a value using a RETURN statement. If the function completes its execution path without encountering a RETURN statement, MySQL will raise Error 1320 to alert you of the oversight.

Diagnosing the Problem

To fix this error, you need to:

  1. Review the Function Code: Check the function for any execution paths that may terminate without hitting a RETURN statement.
  2. Ensure All Conditional Branches Have RETURN: If you have conditional logic like IF-ELSE statements, make sure each branch has a RETURN statement.

Fixing the Error

Here are some examples of how to correct Error 1320 by ensuring that a RETURN statement is present:

  1. Adding a RETURN Statement: CREATE FUNCTION calculate_bonus(employee_id INT) RETURNS DECIMAL(10,2) BEGIN DECLARE bonus DECIMAL(10,2); -- Assume some calculation is done here to determine the bonus SET bonus = 100.00; RETURN bonus; -- Ensure there is a RETURN statement END; In this example, the function concludes with a RETURN statement that provides the calculated bonus.
  2. Handling Conditional Logic: CREATE FUNCTION get_employee_status(employee_id INT) RETURNS VARCHAR(10) BEGIN IF (employee_id <= 1000) THEN RETURN 'senior'; ELSE RETURN 'junior'; END IF; END; This function uses an IF-ELSE statement, and both branches end with a RETURN statement.
  3. Ensuring RETURN in Loops:
    sql CREATE FUNCTION find_max_value() RETURNS INT BEGIN DECLARE max_val INT DEFAULT 0; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO -- Assume some logic to determine max_val SET i = i + 1; END WHILE; RETURN max_val; -- RETURN must be outside the loop END;
    Loops should not necessarily contain the RETURN statement unless the intention is to exit the function from within the loop. Usually, the RETURN statement is placed after the loop concludes.


  • Always provide a default RETURN value to handle unexpected conditions or add an ELSE branch to your conditional logic to ensure a value is returned.
  • Test your function with various inputs to ensure that all execution paths lead to a RETURN statement.
  • Consider the use of CONTINUE HANDLER for SQLSTATE ‘02000’ to provide a RETURN statement in case of no data found situations.

By thoroughly reviewing your stored functions and ensuring that each possible execution path concludes with a RETURN statement, you can fix MySQL Error 1320 and create robust and reliable functions.

Leave a Comment