📋

Description

This error indicates that an `OUT` or `INOUT` parameter in a stored routine (procedure/function) or a `BEFORE` trigger received an argument that isn't a modifiable variable or the `NEW` pseudo-variable. It typically occurs when a literal, expression, or read-only value is supplied where a variable is expected to receive a return value.

💬

Error Message

OUT or INOUT argument %d for routine %s is not a variable or NEW pseudo-variable in BEFORE trigger

🔍

Known Causes

3 known causes

⚠️

Passing Non-Variable to OUT/INOUT

Supplying a literal value, a constant, or an expression to an `OUT` or `INOUT` parameter, which expects a modifiable variable to assign a value to.

⚠️

Incorrect Variable Scope or Declaration

Using a variable that is not properly declared, is out of the current scope, or is otherwise unsuitable for receiving a value from an `OUT` or `INOUT` parameter.

⚠️

Misuse of Pseudo-Variables in BEFORE Trigger

Attempting to use `OLD.column_name` (which is read-only) or a direct column name without the `NEW.` prefix as an `OUT` or `INOUT` argument within a `BEFORE` trigger.

🛠️

Solutions

3 solutions available

1. Ensure OUT/INOUT Parameters are Variables in Stored Procedures easy

Verify that any OUT or INOUT parameters used within stored procedures are declared as variables.

1

When calling a stored procedure that has `OUT` or `INOUT` parameters, ensure that the arguments you pass are actual variables (or `NEW` in a `BEFORE` trigger). You cannot pass literal values directly to `OUT` or `INOUT` parameters. Declare a variable and pass that variable instead.

DELIMITER //
CREATE PROCEDURE my_procedure(IN input_val INT, OUT output_val INT)
BEGIN
  SET output_val = input_val * 2;
END //
DELIMITER ;

-- Incorrect call (will cause error 1414):
-- CALL my_procedure(10, 20);

-- Correct call:
SET @my_output = 0;
CALL my_procedure(10, @my_output);
SELECT @my_output;

2. Use NEW Pseudo-Variable for OUT/INOUT Parameters in BEFORE Triggers medium

In BEFORE triggers, use the 'NEW' pseudo-variable for OUT or INOUT parameters of routines invoked within the trigger.

1

The error message specifically mentions `NEW pseudo-variable in BEFORE trigger`. This means if your `BEFORE` trigger calls a stored procedure or function with `OUT` or `INOUT` parameters, you must use `NEW.column_name` for those parameters, not a literal value or a pre-declared variable outside the trigger's scope. The `NEW` keyword refers to the row that is about to be inserted or updated.

DELIMITER //
CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
  -- Assuming you have a procedure 'update_related_data' that takes an INOUT parameter
  -- and you want to modify a value that will be inserted into my_table.
  -- Let's say 'update_related_data' modifies a value and returns it via an OUT parameter.
  -- For demonstration, let's create a dummy procedure that returns a modified value.
  -- The key is how you'd pass NEW.column_name IF the procedure accepted it directly as INOUT.
  -- More commonly, you'd modify NEW.column_name directly or call a function/procedure
  -- that returns a value to be assigned to NEW.column_name.

  -- Example demonstrating the principle: If you had a procedure that *modified* NEW directly
  -- and returned a status via OUT.
  -- This is a conceptual example as direct INOUT to NEW is less common than modifying NEW itself.
  -- If a procedure expects an INOUT and you want to modify a NEW column:

  -- Let's say `process_new_data` modifies ` NEW.some_column` and returns a status code
  -- via an OUT parameter.
  -- DECLARE status_code INT;
  -- CALL process_new_data(NEW.some_column, status_code);
  -- IF status_code = 1 THEN ...

  -- The error 1414 implies you're trying to pass NEW itself or a column of NEW
  -- as an OUT/INOUT parameter to a routine *called from the trigger*.
  -- The solution is to ensure the routine's parameter is either not OUT/INOUT
  -- or that you are correctly referencing NEW for the assignment.

  -- A more typical scenario for error 1414 in triggers might be:
  -- Suppose you have a procedure `calculate_and_set(IN input_val INT, OUT result_val INT)`
  -- and you try to call it like this within a BEFORE trigger:
  -- CALL calculate_and_set(NEW.some_field, NEW.some_field);
  -- This is wrong if `calculate_and_set` expects a variable for the OUT parameter.
  -- The correct way is to use a temporary variable and then assign it back to NEW.

  SET @temp_result = 0;
  CALL calculate_and_set(NEW.input_field, @temp_result);
  SET NEW.output_field = @temp_result;

  -- OR if the procedure modifies the NEW value directly (less common for OUT/INOUT):
  -- CALL modify_new_row_value(NEW);

END //
DELIMITER ;

3. Refactor Routine to Accept Values Instead of OUT/INOUT Parameters advanced

Modify the stored procedure or function to accept values and return them via a RETURN statement or by modifying IN parameters.

1

If you are frequently encountering this error, it might indicate a design issue with your stored procedures or triggers. Consider refactoring the routine that is causing the error. Instead of using `OUT` or `INOUT` parameters that require variables, have the routine return a value using the `RETURN` statement (for functions) or modify an `IN` parameter if it's a stored procedure that can be designed that way. This simplifies how it can be called, especially from triggers.

-- Original procedure with OUT parameter:
-- CREATE PROCEDURE calculate_value(IN input_val INT, OUT result_val INT)
-- BEGIN
--   SET result_val = input_val * 10;
-- END //

-- Refactored as a function:
DELIMITER //
CREATE FUNCTION calculate_value_func(input_val INT) RETURNS INT
DETERMINISTIC
BEGIN
  RETURN input_val * 10;
END //
DELIMITER ;

-- How to use the function in a trigger:
DELIMITER //
CREATE TRIGGER before_insert_trigger_func
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
  SET NEW.output_field = calculate_value_func(NEW.input_field);
END //
DELIMITER ;

🔗

Related Errors

5 related errors

MySQL Error 1414: Invalid Routine Argument Type