CODEWITHSUNDEEP
c#asp.netsql-serverstored-procedures
Aruna
Aruna

Reputation: 2052

Wait stored procedure execution until previous execution completes in SQL Server

I need to execute a stored procedure in every 30 seconds. I register a method in Application_Start(object sender, EventArgs e) to run in every 30 seconds. This stored procedure takes long time to execute. The issue is whether the previous execution is completed or not the next iteration tries to execute the sp again. This causes some time out issues.

Is there any way to wait for the previous execution get completed before executing the next iteration in sql server?

Upvotes: 1

Views: 2226

Answers (1)

Joe C
Joe C

Reputation: 3993

I generally use Sql Agent for such occasions. It will do what you want automatically.

If thats not an option you could use a semaphore flag. The first thing the SP does is check a table / field for a 1 for in process. If it is 0 then the SP updates to 1, if it is already 1 then the SP exits. At the end of the SP it gets set back to 0.

Upvotes: 2

Related Questions