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
LVL 1
MrKevorkianAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mastooCommented:
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.
0
MrKevorkianAuthor Commented:
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





 
0
mastooCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Snarf0001Commented:
The variables must be declared as output both in the called procedure and the executing statement, ie:

create proc child
0
Snarf0001Commented:
sorry, finger slipped, a moment...
0
Snarf0001Commented:
create proc child
  @param1 output
as
  set @param1 = 5


create proc master
as
begin

  declare @val1
  set @val1 = 2
 
  exec child @val1 output
  print @val1

end

the print statement will print 5 as assigned in proc child
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.