Using WAITFOR
The WAITFOR statement suspends the execution of a connection until either:
- A specified time interval has passed.
- A specified time of day is reached.
The WAITFOR statement is specified with one of two clauses:
- The DELAY keyword followed by an amount_of_time_to_pass before completing the WAITFOR statement. The time to wait before completing the WAITFOR statement can be up to 24 hours.
- The TIME keyword followed by a time_to_execute, which specifies completion of the WAITFOR statement.
This example uses the DELAY keyword to wait for two seconds before performing a SELECT statement:
WAITFOR DELAY '00:00:02'
SELECT EmployeeID FROM Northwind.dbo.Employees
This example uses the TIME keyword to wait until 10 P.M. to perform a check of the pubs specified database to make sure that all pages are correctly allocated and used:
USE pubs
BEGIN
WAITFOR TIME '22:00'
DBCC CHECKALLOC
END
The disadvantage of the WAITFOR statement is that the connection from the application remains suspended until the WAITFOR completes. WAITFOR is best used when an application or stored procedure must suspend processing for some relatively limited amount of time. Using SQL Server Agent or SQL-DMO to schedule a task is a better method of executing an action at a specific time of day.