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 of time. The WAITFOR Command immediately pauses the execution of a Batch, Stored Procedure or Transaction for a given period of time; it even doesn't allow the next Query to process that is executed after the WAITFOR Command in T-SQL.
After the given period of time is reached, the Query Execution engine resumes itself to process 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 between each other 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 had given below an example which 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 for every one 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.