SQL WAITFOR

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.

Also Read..

SQL Server SET XACT_ABORT

SQL Server SET DATEFORMAT

SQL Server SET IDENTITY_INSERT

SQL Server SET NOCOUNT ON | OFF