📋

Description

Error 1313 in MariaDB indicates that a `RETURN` statement has been used outside the scope of a user-defined function. This error typically occurs when attempting to use `RETURN` in a stored procedure, trigger, or event, where it is not syntactically permitted.

💬

Error Message

RETURN is only allowed in a FUNCTION

🔍

Known Causes

3 known causes

⚠️

Using RETURN in a Stored Procedure

This error often occurs when a `RETURN` statement is mistakenly included within a `CREATE PROCEDURE` definition instead of a `CREATE FUNCTION`.

⚠️

Using RETURN in a Trigger Definition

Including a `RETURN` statement within a `CREATE TRIGGER` block will cause this error, as triggers do not support direct return values.

⚠️

Using RETURN in a Scheduled Event

Attempting to use `RETURN` inside a `CREATE EVENT` statement's `DO` block is not allowed, as events execute asynchronously and do not return values.

🛠️

Solutions

3 solutions available

1. Convert Stored Procedure to Stored Function medium

If the intent was to return a value, transform the stored procedure into a stored function.

1

Identify the stored procedure causing the error. Look for `CREATE PROCEDURE` statements that contain `RETURN`.

2

Modify the `CREATE PROCEDURE` statement to `CREATE FUNCTION`. You will need to specify a return data type.

DROP PROCEDURE IF EXISTS my_procedure;

DELIMITER //
CREATE FUNCTION my_function(param1 INT) RETURNS INT
BEGIN
  -- ... your logic here ...
  RETURN some_value;
END //
DELIMITER ;

3

Ensure that the `RETURN` statement is used to return a value of the specified return type. Remove any `RETURN` statements that are not intended to return a value.

4

If the procedure had output parameters, you'll need to adjust the logic to return a single value from the function.

2. Remove Unnecessary RETURN Statement from Stored Procedure easy

If the stored procedure was not intended to return a value, remove the `RETURN` statement.

1

Locate the `CREATE PROCEDURE` statement that contains the `RETURN` keyword.

2

Remove the `RETURN` statement and any associated value. Stored procedures are designed for execution and side effects, not for returning values directly like functions.

-- Original code with error:
-- CREATE PROCEDURE my_procedure() BEGIN
--   -- some logic
--   RETURN 1;
-- END;

-- Corrected code:
CREATE PROCEDURE my_procedure()
BEGIN
  -- some logic
  -- No RETURN statement here
END;

3

If you need to convey a status or result, consider using `SELECT` statements to return data or `SIGNAL` statements for error handling.

CREATE PROCEDURE my_procedure()
BEGIN
  DECLARE status_code INT DEFAULT 0;
  -- some logic that might set status_code
  SELECT status_code;
END;

3. Review and Refactor Trigger Logic medium

Triggers execute automatically and cannot use `RETURN` statements.

1

Identify triggers associated with the table or event that might be triggering this error. Use `SHOW TRIGGERS;` to list all triggers.

SHOW TRIGGERS;

2

Examine the trigger definitions for any `RETURN` statements. Triggers operate in a context where `RETURN` is not permitted.

3

Remove any `RETURN` statements found within trigger definitions. If the intention was to stop execution or signal an error, use `SIGNAL SQLSTATE` instead.

-- Original trigger logic with error:
-- CREATE TRIGGER my_trigger
-- AFTER INSERT ON my_table
-- FOR EACH ROW
-- BEGIN
--   IF NEW.value < 0 THEN
--     RETURN;
--   END IF;
-- END;

-- Corrected trigger logic:
CREATE TRIGGER my_trigger
AFTER INSERT ON my_table
FOR EACH ROW
BEGIN
  IF NEW.value < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Value cannot be negative';
  END IF;
END;

🔗

Related Errors

5 related errors

MariaDB Error 1313: RETURN statement in wrong context