MrKevorkian
asked on
calling sprocs from within a sproc
i have a master stored procedure and i want to call other stored procedures within it.
ie something like this......
--------------------------
@parameter1,
@parameter2,
@parameter3,
exec usp_Child1 @Parameter1
exec usp_Child2 @Parameter2
exec usp_Child3 @Parameter3
--------------------------
how do i do this
thanks
Just like you wrote. You're probably asking how do you work with the returned records in the master proc? If so, you need to make the child procs into functions or load the child proc results into temp tables.
ASKER
yes your right each child proc will return an output value!! it is the primary key of child1, child2, child3 etc.
-------------------------- ---------- ------
@parameter1,
@parameter2,
@parameter3,
@parameter4,
@parameter5,
@parameter6
exec usp_Child1 @Parameter1, @parameter4 output
exec usp_Child2 @Parameter2, @parameter5 output
exec usp_Child3 @Parameter3, @parameter6 ouput
how do i capture the output params of these three child calls
--------------------------
@parameter1,
@parameter2,
@parameter3,
@parameter4,
@parameter5,
@parameter6
exec usp_Child1 @Parameter1, @parameter4 output
exec usp_Child2 @Parameter2, @parameter5 output
exec usp_Child3 @Parameter3, @parameter6 ouput
how do i capture the output params of these three child calls
If you just want the master proc to be able to work with the output parameters of the child procs, what you have should work (assuming you also declare the parameters as outputs in the child proc). You also need to declare your variables as either parameters of the master proc or use a declare statement.
The variables must be declared as output both in the called procedure and the executing statement, ie:
create proc child
create proc child
sorry, finger slipped, a moment...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.