Execute stored procedure sequentially inside another stored procedure

Question

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.

Answer 1

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.

Answer 2

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.