SQL WAITFOR statement is used to blocks the execution of a batch statement, transnational statement or any stored procedure until a specified time or any time interval is reached, or a specified statement modifies or returns at least one row.
It is very useful in such a requirement when SQL script has to wait for some specific time before executing a next statement.
While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction.
WAITFOR statement can be used with query only and cannot be used with CURSOR and VIEWS.
Tips- Including WAITFOR statement can slow the completion of the SQL Server process and can result in a timeout message .
SYNTAX
WAITFOR (DELAY HH:MM:SS OR TIME HH:MM:SS)
WAITFOR DELAY – Is used to pause a query for a certain duration of time. Time to pass before a query is executed. like to delay the execution of a query by any specified time interval.
WAITFOR TIME – Is used to pause a query execution until a specified time of a day is reached. Time to execute, a specific time in a day.
Lets look at an example of WAITFOR with DELAY and TIME
WAITFOR DELAY
Following SQL Statement runs after a delay of 15 second is completed.
SELECT GETDATE() as timethen WAITFOR DELAY '00:00:15' SELECT GETDATE() as timenow
As you can see the time difference between both statement is of 15 sec.
WAITFOR TIME
With the help of TIME option, we can specify the exact future time of the day.
Following SQL Statement runs after specified time has arrived.
DECLARE @waitfortime DATETIME SET @waitfortime = DATEADD(second,15,GETDATE()) SELECT GETDATE() As timethen WAITFOR TIME @waitfortime SELECT GETDATE() As timeNow
As you can see the time difference between both statement is of 15 sec.
6,161 total views, 1 views today