MySQL - Call Multiple Procedures from Within a Procedure

Question

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.

I like the inception in your picture. Transparent console background ;) — Lukasz Szozda
– Lukasz Szozda, Commented Oct 10, 2015 at 8:01

Lukasz Szozda · Accepted Answer · 2015-10-10 08:17:47Z

1

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;

edited Oct 10, 2015 at 8:17

answered Oct 10, 2015 at 8:10

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Magical Gordon Over a year ago

That was such great insight lad - I updated my lastaccessed time with the first call, so the second one had less than a second elapsed, and so I didn't see any changes. This is the kind of bone-headed oversight I fix for my fellow classmates all the time (I volunteer as a teaching assistant). I can't believe I went and made such an obvious mistake, like the ones I fix for others all the time! Ha; just the other day though, I accidentally (in a separate program) wrote "const int pi = 3.1459;" Maybe this is just my bonehead week! Thanks a ton man!!!

Lukasz Szozda Over a year ago

@MagicalGordon By the way, when I could play the game ;)

Magical Gordon Over a year ago

I'm leading a team in a club @ my school and we're pushing to get our minimum viable product out within 4 weeks from now. Of course it almost entirely relies on me since I'm doing everything except for the frontend HTML/CSS. I'm trying to get lots of logic into my DB to reduce PHP code and network calls etc, I just think writing procedures in SQL is super cool. So yeah 4 weeks maybe? Totally unfinished product here: github.com/candyapplecorn/php-mysql-game

Lukasz Szozda Over a year ago

@MagicalGordon Great to hear it. Good luck with development and remeber SO community will help ;)

Collectives™ on Stack Overflow

MySQL - Call Multiple Procedures from Within a Procedure

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related