MySQL trigger calling stored procedure always get null value for out parameter of stored procedure

Question

My stored procedure OUT parameter, always return a null value.

Here is sample Table, Trigger and Procedure code.

Table: test
Columns:

• id - Int

• status - enum(‘pass’, ‘fail’)

  • status - enum(‘pass’, ‘fail’) (null is allowed)

Values in a table:

id  |  status
1   |  null

Trigger:

create trigger BEFORE_UPDATE_TEST before update on `test` for each row begin

    call Test_BEFORE_UPDATE_TEST(old.id, @updatedStatus);

       ## I always get @updatedStatus null/nil

    if (@updatedStatus is not null and @updatedStatus <> new.status) then
        set new.status = @updatedStatus;
    end if;

end;

Procedure:

create procedure Test_BEFORE_UPDATE_TEST (
  IN id int(5),
  OUT status enum(‘pass’, ‘fail’)
)
begin
   @status = ‘pass’;

END;

What is wrong with this code, as I get unexpected result as null in the value @updatedStatus, which should be 'pass'.

I looked around following QAs on dba.stackexchange but could't find solution.

I use MySQLWorkbench in MacOS Catalina and version of MySQL is 8.0.19.

Answer 1

Can you please elaborate in detail. (Help me to understand more in detail) – Krunal

create procedure Test_BEFORE_UPDATE_TEST (
  IN id int(5),
  OUT status enum('pass', 'fail')
)
begin
/* wrong statement - assigns to another variable
   @status = 'pass';
*/
   status = 'pass';

END;

status is local variable - it is defined on procedure level and has procedure scope (it is created at procedure start, it is inaccessible outside the procedure, it will be destroyed when the procedure finishes its work, if the procedure will be called again then new instance of a variable named status will be created). Also it has a definite datatype which cannot be changed, and non-matching datatype error may be generated during wrong usage/assignement. See Variables in Stored Programs.

@status is user-defined variable. it is defined on connection level and has connection scope (it is created when accessing first time within the connection, it is accessible outside the procedure, even after the procedure ends its work, it does not change its value between procedure calls - but may be changed by some another code within this connection). Also it have no definite datatype, and its datatype may be changed by the assignement (moreover, you cannot predict its real datatype in most cases). See User-Defined Variables.