question

Poornima-2732 avatar image
0 Votes"
Poornima-2732 asked ErlandSommarskog commented

Execute stored procedure sequentially inside another stored procedure

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.

sql-server-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Do you observe the same issues if you temporarily set “Max Degree of Parallelism” to 1? (See: https://docs.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.


0 Votes 0 ·

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

0 Votes 0 ·

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

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Poornima-2732 avatar image
0 Votes"
Poornima-2732 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ErlandSommarskog commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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.

1 Vote 1 ·
Poornima-2732 avatar image
0 Votes"
Poornima-2732 answered

Please look below example.

46295-image.png


46244-image.png


46215-image.png


46140-image.png



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.


image.png (5.8 KiB)
image.png (17.9 KiB)
image.png (19.3 KiB)
image.png (9.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

As Olaf said, there are several problems with what you posted.

First, getdate() is only accurate to 3ms. So basing your problem on the result of getdate() is not really valid. If you want more accuracy you must use datetime2(7) data type.

Second, your code does not stop on error. If you call a stored proc from an outer proc, and the called proc fails, it does not terminate the master proc. If you want that to happen you need to trap the error.

 DROP TABLE IF EXISTS dbo.test;
 GO
 CREATE TABLE dbo.test (name varchar(50), createdate datetime2(7));
 GO
 CREATE UNIQUE INDEX uidx_test ON dbo.test (name, createdate);
 GO
 CREATE PROC dbo.testinsert (@pname varchar(50), @pcreatedate datetime2(7))
 AS
 BEGIN
     INSERT INTO dbo.test values (@pname, @pcreatedate)
 END
 GO
 CREATE PROC dbo.testmaster
 AS
 BEGIN
 BEGIN TRY
     DECLARE @v datetime2(7); 
     SET @v = SYSDATETIME();
    
     EXEC dbo.testinsert 'test1',@v;
     EXEC dbo.testinsert 'test1',@v;
    
    
     SET @v = SYSDATETIME();
    
     EXEC dbo.testinsert 'test1',@v;
     EXEC dbo.testinsert 'test3',@v;
 END TRY
 BEGIN CATCH
     THROW;
 END CATCH
    
 END
 GO
    
 EXEC dbo.testmaster;

PS. Please do not post screen shots. You can use the icon on the top bar to post code snippets.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Only to add what Tom and others said. Your issue is not about procedures executing in parallel. It is a combination of code running faster than you expect and poor error handling.

Error handling in SQL Server is a messy topic, because there are so many inconsistences. I am not going to say that much here, but refer to my primer on error handling which you find here: http://www.sommarskog.se/error_handling/Part1.html
Yes, there are more parts than Part One, but Part One is the short one, and you may prefer to stop there for now.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.