Solved

calling sprocs from within a sproc

Posted on 2004-10-29
193 Views
Last Modified: 2008-03-06

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
0
Question by:MrKevorkian
    6 Comments
     
    LVL 21

    Expert Comment

    by:mastoo
    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
     
    LVL 1

    Author Comment

    by:MrKevorkian
    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
     
    LVL 21

    Expert Comment

    by:mastoo
    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
     
    LVL 22

    Expert Comment

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

    create proc child
    0
     
    LVL 22

    Expert Comment

    by:Snarf0001
    sorry, finger slipped, a moment...
    0
     
    LVL 22

    Accepted Solution

    by:
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Lean Six Sigma Project Manager Certification

    There are many schools of thought around successful project management, but few as highly regarded as the Six Sigma and Lean methods. With 37 hours of learning, this training will explain concrete processes for increasing efficiency and limiting wasted time and effort.

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    5 Experts available now in Live!

    Get 1:1 Help Now