SP Flow of Control

29 Jun 2006 2:27 PM

Brian Shannon

What happens when you run a SP inside of another SP?

My case:

At the beginning of the SP I want to call another SP. If I do this will the
called SP finish running before proceeding to run the rest of the code? Or
do they run simultaneously?

Thanks.

29 Jun 2006 2:36 PM

Tracy McKibben

Brian Shannon wrote:

> What happens when you run a SP inside of another SP?
>
> My case:
>
> At the beginning of the SP I want to call another SP. If I do this will the
> called SP finish running before proceeding to run the rest of the code? Or
> do they run simultaneously?
>
> Thanks.
>
>

The "outer" SP will wait for the "inner" SP to complete before continuing...

29 Jun 2006 2:40 PM

Immy

Your calling proc (top level proc) will wait for the called proc (inner
proc) to complete before continuing.

Show quote

"Brian Shannon" <brian.shan***@diamondjo.com> wrote in message
news:%23Qyiqg4mGHA.1204@TK2MSFTNGP03.phx.gbl...
> What happens when you run a SP inside of another SP?
>
> My case:
>
> At the beginning of the SP I want to call another SP. If I do this will
> the called SP finish running before proceeding to run the rest of the
> code? Or do they run simultaneously?
>
> Thanks.
>

29 Jun 2006 2:45 PM

Aaron Bertrand [SQL Server MVP]

If you want the second SP to run asynchronously, create a job and call
msdb..sp_start_job

Otherwise, the first SP will wait for the second SP to finish before
continuing on its task. Since the first SP is a unit, you wouldn't want to
move to step 2 if it relied on the completion of step 1. SQL Server must
step through the stored procedure step by step, because it has no way to
know that you do not need to wait for a step to finish before moving on to
the next step.

Show quote

"Brian Shannon" <brian.shan***@diamondjo.com> wrote in message
news:%23Qyiqg4mGHA.1204@TK2MSFTNGP03.phx.gbl...
> What happens when you run a SP inside of another SP?
>
> My case:
>
> At the beginning of the SP I want to call another SP. If I do this will
> the called SP finish running before proceeding to run the rest of the
> code? Or do they run simultaneously?
>
> Thanks.
>