Execute stored procedure sequentially inside another stored procedure

Question

Execute stored procedure sequentially inside another stored procedure

Poornima 21

We have a master procedure which calls many procedures inside as shown below.

exec SP1
exec SP2
exec SP1

We observe the procedure SP1 is triggered and immediately procedure SP2 also triggered. The second line does not wait for completion of execution of first line.

I tried using WAITFOR DEALY and it works in few places but not in all places as I do not know how much time I need to wait for the 1st procedure to complete its execution.

Please help me with any other option available to wait till the first procedure or sql gets complete.

Viorel 122.5K Reputation points

2020-12-08T16:16:59.763+00:00

Do you observe the same issues if you temporarily set “Max Degree of Parallelism” to 1? (See: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option). Although, this is probably unrelated to your problems.
EchoLiu-MSFT 14,621 Reputation points

2020-12-11T09:50:26.923+00:00

Experts have provided some solutions. Has your problem been solved? Besides, do you have any updates？
Please also remember to mark the replies as answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

Echo
EchoLiu-MSFT 14,621 Reputation points

2020-12-15T09:25:15.083+00:00

Do you have any updates?
Please also remember to mark the replies as answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

Regards
Echo

7 answers

Your answer

Viorel 122.5K Reputation points

2020-12-08T16:16:59.763+00:00

Do you observe the same issues if you temporarily set “Max Degree of Parallelism” to 1? (See: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option). Although, this is probably unrelated to your problems.
EchoLiu-MSFT 14,621 Reputation points

2020-12-11T09:50:26.923+00:00

Experts have provided some solutions. Has your problem been solved? Besides, do you have any updates？
Please also remember to mark the replies as answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

Echo
EchoLiu-MSFT 14,621 Reputation points

2020-12-15T09:25:15.083+00:00

Do you have any updates?
Please also remember to mark the replies as answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

Regards
Echo

Answer 1

Olaf Helper 47,436

A SQL batch is always execute in sequence, there is no option to change the behaviour.
Why do think it's not executed in sequence?

Answer 2

Poornima 21

Procedures are executed in sequence but it does not wait till the execution gets completed. Looks like exec procedures are running in multiple threads in parallel.
We don t want to start executing the next lines of code until the first line execution completes.

Answer 3

Olaf Helper 47,436

No, a SQL batch runs in sequence, the execution waits until SP1 has finished, before the execution of SP2 starts.

Phú Đạt Nguyễn 1 Reputation point

2022-04-02T18:21:09.407+00:00

I have a PS1 that gets data from Elasticsearch, if I execute PS1 it executes in 4 minutes to get the insert data into a dbo.ABC physical table. When I put it in PS2. PS2 still runs fine. Until I install a job to run PS2 automatically, the job only executes for 15 seconds. I look at the job execution history and there is no error.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-04-02T18:35:57.167+00:00

If you have a problem, you should start a new thread, describing your problem from start to end. As a tip: The description you have given above is confusing and you need to give a lot more details.

Answer 4

Poornima 21

Please look below example.

There is an error but still the procedure executes the last statement. Also i have written simple insert query but actually my code have lot more statements but i clearly observe it does not wait till execution completes.

Answer 5

Olaf Helper 47,436

Datetime has a precision of 3 ms, INSERT into such a small table with such small values are pretty fast, faster then 3 ms; so your test proofs nothing then it's fast.

Bjorn Ottosson 0 Reputation points

2023-06-08T16:15:27.5933333+00:00

Absolutely SQL executes code in sequence, line by line, whether inside a called procedure or not, else... well, it would be useless. First 3 calls here are too fast for datetime2 but still sequential. Here is the proof:

Share via

Execute stored procedure sequentially inside another stored procedure

7 answers

Your answer