CODEWITHSUNDEEP
mysqlstored-procedurestriggersprocedureout-parameters
Krunal
Krunal

Reputation: 79646

MySQL procedure call from trigger always returns null for out parameters

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

Here is sample Table, Trigger and Procedure code.

Table: test
Columns:

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 stackoverflow but could't find solution.

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

Upvotes: 0

Views: 568

Answers (1)

slaakso
slaakso

Reputation: 9050

The OUT status parameter in the procedure is different from the user defined variable @status. These are two different variable types altogether.

So, the procedure should look like:

create procedure Test_BEFORE_UPDATE_TEST (
IN id int(5),
OUT status enum('pass', 'fail')
)
begin
 set status = 'pass';
END;

In the trigger, you should also use normal variables declared with DECLARE:

create trigger BEFORE_UPDATE_TEST 
before update on test for each row 
begin

declare v_status enum('pass', 'fail');

call Test_BEFORE_UPDATE_TEST(old.id, v_status);

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

end;

Upvotes: 2

Related Questions