I'm trying to create a parent type stored procedure that houses a number of other sprocs. Some of the child sprocs contain inserts and have 1 or more (usually 1) output params.
What's the best way to evaluate these params within the parent sproc?
The zero below is the placeholder for the output param. Not sure if this is necessary, but it's what I've done in the past. I've already declared any variables that are listed in the code, but I didn't include that part here.
Inside the parent sproc I'm trying these stmts, all to no avail.
Code:
CALL DBname.Sproc1(0) into iVariable ; <-- this doesn't work.
Set iVariable = (CALL DBname.Sproc1(0) into iUserID); <-- this doesn't work.
Set iVariable = (CALL DBname.Sproc1(0) into iUserID;); <-- this doesn't work.
if iVariable=1 then
-- DO SOMETHING.
end if;
If I bring only 1 output param back, what's the best way to handle (and one that works!). What's needed if 2 or more are brought back? Temporary table?
Can transactions be handled at the parent level, or will each sproc be it's own entity, handling inserts within themselves. What I mean by this, if there's an insert stmt in a child and I don't commit here, can I commit in the parent instead?
Any help is appreciated! Thanks!
SkydiverMN
dBforums
>
stored procedure in stored procedure, trying to return output params
08-20-09, 11:28
stored procedure in stored procedure, trying to return output params
Linear Mode
