CODEWITHSUNDEEP
mysqlstored-procedures
Magical Gordon
Magical Gordon

Reputation: 404

MySQL - Call Multiple Procedures from Within a Procedure

I want to call multiple procedures from within a procedure. In the following SQL, I create three procedures. upd_r_money and upd_r_fuel both work as expected when called individually from the command line. When I call upd_all, only the first call within upd_all is run; the second call to upd_r_money doesn't run.

I can't figure out why this happens - maybe something in my upd_r_fuel procedure causes my upd_all procedure to end early? I am a newby to writing procedures, and SQL in general.

There was another question here about this problem, but the answer is exactly what I'm already doing, and the answer's link was down.

drop procedure upd_r_money;
delimiter //
CREATE procedure upd_r_money(row_id int)
BEGIN
DECLARE money_rate INT DEFAULT 1;
DECLARE period INT DEFAULT 0;
SET period = (select timestampdiff(second, (select lastaccessed from gamerows where id = row_id), now()));
update gamerows
set money = money + period * money_rate,
lastaccessed = now()
where id = row_id;
END;
//
delimiter ;

drop procedure upd_r_fuel;
delimiter //
CREATE procedure upd_r_fuel(row_id int)
fuel: BEGIN
DECLARE fuel_rate INT DEFAULT 1;
DECLARE period INT DEFAULT 0;
SET period = (select timestampdiff(second, (select lastaccessed from gamerows where id = row_id), now()));
update gamerows
set fuel = fuel + period * fuel_rate,
lastaccessed = now()
where id = row_id;
END fuel;
//
delimiter ;

drop procedure upd_all;
delimiter //
CREATE PROCEDURE upd_all(row_id int)
BEGIN
call upd_r_fuel(row_id);
call upd_r_money(row_id);
END;
//
delimiter ;

If I copy and paste the above SQL commands, my procedures are created successfully with no errors and I can call all three of them. However as I wrote earlier, upd_all seems to stop after calling its first procedure within. If I switch upd_r_money with upd_r_fuel, the same behavior occurs - the first procedure is called and not the second.

enter image description here

Upvotes: 3

Views: 1575

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

I suspect that it doesn't work as expected because you update lastaccessed time and calculate difference with NOW. First work because there is significant difference. But with second stored procedure you have timestammpdiff between NOW() and NOW() - miliseconds.

Check if removing in first stored procedure lastaccessed from update helps.

drop procedure upd_r_money;
delimiter //
CREATE procedure upd_r_money(row_id int)
BEGIN
DECLARE money_rate INT DEFAULT 1;
DECLARE period INT DEFAULT 0;
SET period = (select timestampdiff(second, (select lastaccessed from gamerows where id = row_id), now()));
update gamerows
set money = money + period * money_rate
where id = row_id;
END;
//
delimiter ;

Warning: Now the order of execution matters.

Also your stored procedures are so similiar that I would combine them in one UPDATE:

update gamerows
set fuel = fuel + period * fuel_rate,
    money = money + period * money_rate,
    lastaccessed = now()
where id = row_id;

Upvotes: 1

Related Questions