📋

Description

Error 1320 in MariaDB indicates that a stored function has been defined without a mandatory `RETURN` statement. Stored functions are expected to return a single scalar value, and this error occurs when the function's definition does not explicitly provide a return path for all execution branches.

💬

Error Message

No RETURN found in FUNCTION %s

🔍

Known Causes

3 known causes

⚠️

Missing RETURN Statement

The most common cause is simply forgetting to include a `RETURN expression;` statement within the function's body, which is essential for all MariaDB functions.

⚠️

Incomplete Conditional Logic

If a function uses `IF...THEN...ELSE` or other conditional statements, a `RETURN` might be present in some branches but missing in others, leading to an undefined return path for certain conditions.

⚠️

Incorrect Function Definition

Attempting to define a stored procedure (which doesn't require a return value) using `CREATE FUNCTION` syntax will trigger this error if no `RETURN` statement is included.

🛠️

Solutions

3 solutions available

1. Add a RETURN Statement to the Function easy

The most direct solution is to ensure your function explicitly returns a value.

1

Identify the function causing the error. The error message `MariaDB Error 1320: Missing RETURN Statement in Function %s` will usually contain the name of the function.

2

Modify the function's definition to include a `RETURN` statement. This statement should specify the value to be returned. If the function is meant to return `NULL` in certain scenarios, ensure there's a `RETURN NULL;` statement.

DELIMITER //
CREATE FUNCTION your_function_name (param1 INT) RETURNS INT
BEGIN
  DECLARE result INT;
  -- ... your function logic ...
  SET result = param1 * 2;
  RETURN result; -- This is the crucial part
END //
DELIMITER ;

3

Recreate or alter the function in your MariaDB database.

DROP FUNCTION IF EXISTS your_function_name;
-- Paste the modified CREATE FUNCTION statement from the previous step
DELIMITER //
CREATE FUNCTION your_function_name (param1 INT) RETURNS INT
BEGIN
  DECLARE result INT;
  SET result = param1 * 2;
  RETURN result;
END //
DELIMITER ;

2. Ensure All Execution Paths Return a Value medium

Verify that every possible logical path within your function ends with a RETURN statement.

1

Review the logic of the function. Pay close attention to conditional statements (IF, CASE) and loops (WHILE, REPEAT).

2

For each `IF` or `CASE` branch, ensure there is a `RETURN` statement. If a condition is not met and the function doesn't explicitly return, it can lead to this error.

DELIMITER //
CREATE FUNCTION check_value (input_val INT) RETURNS VARCHAR(50)
BEGIN
  DECLARE output_msg VARCHAR(50);
  IF input_val > 10 THEN
    SET output_msg = 'Greater than 10';
    RETURN output_msg; -- RETURN in IF block
  ELSEIF input_val < 0 THEN
    SET output_msg = 'Negative';
    RETURN output_msg; -- RETURN in ELSEIF block
  ELSE
    SET output_msg = 'Between 0 and 10';
    RETURN output_msg; -- RETURN in ELSE block
  END IF;
END //
DELIMITER ;

3

If your function has loops, ensure that either the loop always completes and a `RETURN` statement follows, or that a `RETURN` statement is placed within the loop under certain conditions that guarantee exit and return.

DELIMITER //
CREATE FUNCTION find_first_even (limit_val INT) RETURNS INT
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < limit_val DO
    IF i % 2 = 0 THEN
      RETURN i; -- RETURN from within the loop
    END IF;
    SET i = i + 1;
  END WHILE;
  RETURN -1; -- RETURN if no even number found within limit
END //
DELIMITER ;

4

Recreate or alter the function after ensuring all paths have a `RETURN` statement.

DROP FUNCTION IF EXISTS check_value;
-- Paste the corrected CREATE FUNCTION statement
DELIMITER //
CREATE FUNCTION check_value (input_val INT) RETURNS VARCHAR(50)
BEGIN
  DECLARE output_msg VARCHAR(50);
  IF input_val > 10 THEN
    SET output_msg = 'Greater than 10';
    RETURN output_msg;
  ELSEIF input_val < 0 THEN
    SET output_msg = 'Negative';
    RETURN output_msg;
  ELSE
    SET output_msg = 'Between 0 and 10';
    RETURN output_msg;
  END IF;
END //
DELIMITER ;

3. Use a Default RETURN Statement for Unforeseen Paths easy

Add a final RETURN statement to catch any execution paths not explicitly handled by conditional logic.

1

Examine the function's code for any potential execution paths that might not be covered by your `IF` or `CASE` statements.

2

At the end of the function's `BEGIN...END` block, add a `RETURN` statement that provides a default value. This is often `NULL` if no specific value can be determined.

DELIMITER //
CREATE FUNCTION complex_logic (param1 INT, param2 VARCHAR(10)) RETURNS VARCHAR(50)
BEGIN
  DECLARE result VARCHAR(50);
  IF param1 > 100 THEN
    SET result = CONCAT('Large: ', param2);
    RETURN result;
  ELSEIF param2 = 'Special' THEN
    SET result = 'Special Case';
    RETURN result;
  END IF;
  -- If neither of the above conditions are met, we need a default return.
  RETURN NULL;
END //
DELIMITER ;

3

Recreate the function with the added default `RETURN` statement.

DROP FUNCTION IF EXISTS complex_logic;
-- Paste the modified CREATE FUNCTION statement
DELIMITER //
CREATE FUNCTION complex_logic (param1 INT, param2 VARCHAR(10)) RETURNS VARCHAR(50)
BEGIN
  DECLARE result VARCHAR(50);
  IF param1 > 100 THEN
    SET result = CONCAT('Large: ', param2);
    RETURN result;
  ELSEIF param2 = 'Special' THEN
    SET result = 'Special Case';
    RETURN result;
  END IF;
  RETURN NULL;
END //
DELIMITER ;

🔗

Related Errors

5 related errors