I have a nested MySQL stored procedure that's basically structured like this:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_main`(in param1 int, in param2 int)
BEGIN
declare refid int;
# do some stuff here
select refid;
call sp_child(param1, param2, refid);
END
Both sp_main and sp_child are stored procedures, the problem is sp_child itself has a return value (call it refid2) and when I use sqlquery in RODBC I always ended up getting refid2 instead of refid (which is what I need), although when I run it in MySQL Workbench I ended up seeing both refid and refid2 when call sp_main(...). All sp_child param are "in" params and the only "out" value is returned in the end of sp_child call.
Is there any way I could either:
Change the code in sp_main so that the output in sp_child is does not gets shown? I tried to use set @output = call sp_child(...); to suppress the output showing up when calling sp_main but that ended up giving me a syntax error. The sp_child unfortunately can not be modified as it is shared with several other SPs and changing the params would require changing all of the dependent SPs. Also obviously making sp_child to become a function is also out of question.
OR
Somehow use sqlquery in RODBC (or another R package or function) to somehow get both the output from the sp_child call AND sp_main call?
Thanks!