4

I have a MYSQL stored procedure SP1() that returns a result set.

I want to call SP1() inside of SP2() and loop through the result set of SP1() to do some additional work.

I don't want to include my logic from SP1() because it would make SP2() too complicated.

Any suggestions?

Thanks.

makstaks
  • 2,111
  • 4
  • 24
  • 38

2 Answers2

4

What you want to do doesnt sound particularly good and maybe you should think about re-designing those 2 procs. However, you could do something like this as a quick fix:

get your sp2 sproc to write it's intermediate results to a temporary table which you can then access/process inside of sp1. You can then drop the temporary table which you created in sp2 once sp1 returns.

http://pastie.org/883881

delimiter ;
drop procedure if exists foo;
delimiter #

create procedure foo()
begin

  create temporary table tmp_users select * from users;

  -- do stuff with tmp_users

  call bar();

  drop temporary table if exists tmp_users;

end #

delimiter ;

drop procedure if exists bar;

delimiter #

create procedure bar()
begin
  -- do more stuff with tmp_users
  select * from tmp_users;
end #

delimiter ;

call foo();

not very elegant but should do the trick

Jon Black
  • 16,223
  • 5
  • 43
  • 42
  • yeah, i did end up re-designing the stored procedures, the solution is close to your suggestion. SP1() now creates a temp table and then using a cursor for each row I read I call SP2(). I don't know if this is good practice, but it works for now. – makstaks Apr 02 '10 at 21:37
  • @f00 I have same scenario as hmak I used temp table in SP2() and return temp table in sp2() then use temp table in sp1() it is working fine for me but don't know this is good practice,Let me know this is good practice ?code create procedure foo() begin call bar(); select tmptbl; drop temporary table if exists tmp_users; end # – MSTdev Apr 09 '14 at 05:16
-2

Cursors would help solve the issue.

I am not sure if this is possible but make a cursor for select call for SP1() and iterate over them as usual cursor.

Kapil D
  • 2,662
  • 6
  • 28
  • 30