📋

Description

Error 1321, 'FUNCTION %s ended without RETURN', indicates that a MySQL stored function terminated its execution without explicitly returning a value. Stored functions are designed to always return a single scalar value, and this error occurs when the function's logic allows it to complete without executing a `RETURN` statement.

💬

Error Message

FUNCTION %s ended without RETURN

🔍

Known Causes

3 known causes

⚠️

Missing RETURN Statement

The function body simply omits a `RETURN` statement, which is mandatory for all MySQL stored functions.

⚠️

Incomplete Conditional Logic

Within conditional statements (e.g., `IF`, `CASE`), not all possible execution paths include a `RETURN` statement, leading to an unhandled exit.

⚠️

Uncaught Loop Exit

A loop within the function might terminate or exit prematurely without ensuring a `RETURN` statement is executed afterwards or within the loop's logic.

🛠️

Solutions

3 solutions available

1. Add a RETURN Statement to All Possible Execution Paths medium

Ensure every logical branch within the stored function has a RETURN statement.

1

Review the stored function code carefully. Identify all conditional blocks (IF, CASE, WHILE, LOOP) and ensure that each path through these blocks eventually leads to a RETURN statement.

2

If a conditional block might complete without explicitly returning a value, add a default RETURN statement at the end of the function or within that block to handle such cases.

DELIMITER //

CREATE FUNCTION my_function(param INT) RETURNS INT
BEGIN
  DECLARE result INT;
  IF param > 10 THEN
    SET result = param * 2;
  ELSEIF param < 5 THEN
    SET result = param / 2;
  ELSE
    SET result = param;
  END IF;

  -- Add a RETURN statement here to cover the ELSEIF and ELSE paths if they don't implicitly return.
  -- In this specific example, the ELSEIF and ELSE paths also assign to 'result', so a final RETURN is needed.
  RETURN result;
END //

DELIMITER ;

3

Recreate the stored function with the added RETURN statements.

DROP FUNCTION IF EXISTS my_function;

DELIMITER //

CREATE FUNCTION my_function(param INT) RETURNS INT
BEGIN
  DECLARE result INT;
  IF param > 10 THEN
    SET result = param * 2;
    RETURN result; -- Explicit return for this path
  ELSEIF param < 5 THEN
    SET result = param / 2;
    RETURN result; -- Explicit return for this path
  ELSE
    SET result = param;
    RETURN result; -- Explicit return for this path
  END IF;
  -- If the IF/ELSEIF/ELSE structure is complex, a final RETURN might still be necessary
  -- or the above explicit RETURNS are sufficient.
END //

DELIMITER ;

2. Use a Default RETURN Statement easy

Add a final RETURN statement at the end of the function to catch any unhandled execution paths.

1

Add a `RETURN` statement at the very end of your stored function definition. This ensures that if the execution flow somehow bypasses all other explicit `RETURN` statements, a value is still returned.

DELIMITER //

CREATE FUNCTION calculate_value(input_val INT) RETURNS INT
BEGIN
  DECLARE output_val INT;

  IF input_val IS NULL THEN
    SET output_val = 0;
  ELSEIF input_val > 100 THEN
    SET output_val = 100;
  ELSE
    SET output_val = input_val;
  END IF;

  -- This RETURN statement will be executed for all paths that assign to output_val
  RETURN output_val;
END //

DELIMITER ;

2

If your function doesn't explicitly assign a value in all branches, ensure the default RETURN handles it. For example, if a branch doesn't assign to a variable that is then returned, you might need to return a default value directly.

DELIMITER //

CREATE FUNCTION process_data(id INT) RETURNS VARCHAR(255)
BEGIN
  DECLARE data_found VARCHAR(255);

  SELECT column_name INTO data_found FROM your_table WHERE primary_key = id;

  IF data_found IS NULL THEN
    -- If no data is found, the function might exit without returning anything.
    -- Explicitly return a default value or an indicator.
    RETURN 'NOT_FOUND';
  END IF;

  RETURN data_found;
END //

DELIMITER ;

3. Ensure Variable Assignment Before RETURN medium

Verify that the variable being returned has been assigned a value in all relevant execution paths.

1

Identify the variable that your function is intended to return. Trace all possible execution paths to ensure this variable is assigned a value before any `RETURN` statement that uses it.

2

If a variable might not be assigned in a specific branch, initialize it with a default value before the conditional logic or assign a default value within that branch.

DELIMITER //

CREATE FUNCTION get_status(code INT) RETURNS VARCHAR(50)
BEGIN
  DECLARE status_message VARCHAR(50) DEFAULT 'UNKNOWN'; -- Initialize with a default

  IF code = 1 THEN
    SET status_message = 'SUCCESS';
  ELSEIF code = 0 THEN
    SET status_message = 'FAILURE';
  END IF;
  -- If code is neither 1 nor 0, status_message remains 'UNKNOWN', which is then returned.

  RETURN status_message;
END //

DELIMITER ;

🔗

Related Errors

5 related errors

MySQL Error 1321: Function Missing RETURN