Put SQL Server’s Query Execution Engine on Hold
In Microsoft SQL Server, there is an option available to make the Query Execution Engine wait for a specified time or time interval reached. That's the WAITFOR Command. This is very similar to the Sleep Method in Thread Execution.
The WAIT FOR Command makes the SQL Server's Query Execution engine wait a specified period. The WAITFOR Command immediately pauses the execution of a Batch, Stored Procedure, or Transaction for a given period; it doesn't allow the next Query to process executed after the WAITFOR Command in T-SQL Server.
After the given period is reached, the Query Execution engine resumes processing the next Query that was placed immediately after the WAITFOR Command in a Batch, Stored Procedure, or Transaction.
The WAITFOR Command is used with two clauses, DELAY and TIME. There is a small difference, but both do the same thing.
The difference between WAITFOR DELAY and WAITFOR TIME is:
- WAITFOR DELAY, Holds query execution for the specified time interval.
- WAITFOR TIME, Holds query execution until the specified time has been reached.
WAITFOR DELAY
WAITFOR DELAY holds query execution for the specified time interval.
The syntax to make a transaction wait for 25 Seconds is:
WAITFOR DELAY '00:00:25' /* TIME FORMAT HH:MM:SS */
And to make a transaction wait for 10 hours, 35 minutes, and 40 Seconds is:
WAITFOR DELAY '10:35:40' /* TIME FORMAT HH:MM:SS */
WAITFOR TIME
WAITFOR TIME holds query execution until the specified time has been reached.
The syntax to make a program wait until the next occurrence of 3 AM would be:
WAITFOR TIME '03:00:00' /* TIME FORMAT HH:MM:SS *
The syntax to make a program wait until the next occurrence of 5.30 PM would be:
WAITFOR TIME '17:30:00' /* TIME FORMAT HH:MM:SS */
Example
I have given below an example that works with the NORTHWIND Database. Execute it and see the difference between them.
/* WAITFOR DELAY */
use NORTHWIND
Go
BEGIN
SELECT * FROM dbo.EMPLOYEES
/* HOLDS QUERY EXECUTION FOR THE SPECIFIED AMOUNT OF TIME INTERVAL */
WAITFOR
DELAY '00:00:05' /* TIME FORMAT HH:MM:SS */
END
/* WAITFOR TIME */
use NORTHWIND
Go
BEGIN
SELECT * FROM dbo.EMPLOYEES
/* HOLDS QUERY EXECUTION TILL THE SPECIFIED TIME REACHED */
WAITFOR
TIME '07:30:30' /* TIME FORMAT HH:MM:SS */
END
Triggering an Event
The WAITFOR Command is also used to Trigger an event. This can be done by integrating the WAITFOR Command with a While loop.
--DECLARE @COUNT INT
--SET @COUNT = 1
WHILE(SELECT COUNT(*) FROM dbo.EMPLOYEES WHERE HIREDATE = GETDATE()) = 0
BEGIN
/* MAKES THE ABOVE WHILE LOOP TO EXECUTE FOR EVERY 1 SECOND TILL THE CONDITION BECOMES FALSE */
WAITFOR
DELAY '00:00:01' /* TIME FORMAT HH:MM:SS */
--PRINT N'THIS IS '+CAST(@COUNT AS VARCHAR(20))+' TIME'
--SET @COUNT = @COUNT + 1
END
In the preceding Example, the loop checks the employee table for a new employee every second until the count becomes true. As long as the Count is 0 (Zero), the WAITFOR Command causes the loop to wait for one second before checking again.