Sample Header Ad - 728x90

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

-2 votes
1 answer
1421 views
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.
Asked by Krunal (101 rep)
Apr 13, 2020, 04:53 AM
Last activity: Apr 13, 2020, 10:59 AM