Using WAITFOR to run a SQL Statement on a given time

Recently I came across a situation where I wanted to run the same SP at the same exact time from several sessions. The purpose of this was to simulate and see if there are any blocking or deadlock situations. As this is not possible to do by manually pressing F5 from few sessions, I looked out for a solution.

And I found this WAITFOR function in SQL Server very much do the trick. WAITFOR function has two behaviors one we can give a DELAY, a specific number of hours:minutes:seconds to wait till it executes the next statement(s) in the batch. Or by using TIME we can provide a specific time to run the next statement(s) in the batch.

Usage would be as follows;

WAITFOR DELAY '00:00:30'

EXECUTE spSampleSP @ID = 23945

When we run the above script the process will wait 30 seconds before it executes second statements which in this case the stored procedure.

WAITFOR TIME '13:11:30'

EXECUTE spSampleSP @ID = 23945

When we use the above script with the TIME function it will wait till the system time reaches 1 PM, 11 minutes and 30 seconds to execute the stored procedure.

So I used WAITFOR TIME in my situation where I could set same time for few sessions to execute the same stored procedure and could simulate the scenario. Hope you will also find this useful.