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:
Columns: -
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
Last activity: Apr 13, 2020, 10:59 AM