The WAITFOR statement is used to stop execution of current transaction/Stored procedure until delay time or specified time.
It can be used with different combinations like DELAY, TIME or till for service brokers receive message.
1. DELAY is used if we want to stop execution for certain amount of time. Its syntax is as below.
WAITFOR DELAY 'delayTimeAmount'
If I want to delay execution for 5 seconds, then it can be written as below.
WAITFOR DELAY '00:00:05'
Here delay time is specified HH:MM:SS format.
2. TIME option is used in case we can wait until particular time. For example if I want to wait execution till 13:15 then I'll go for TIME option. Its syntax is as below.
WAITFOR TIME 'time'
3. When we are working with service broker and we want to wait until message has arrived, then we can use as specified in below syntax.
WAITFOR (RECEIVE 'receive query')
If we don't know, how much time WAITFOR take and we will want to set timeout we can do this by specifying TIMEOUT after WAITFOR statement.
The actual time to delay can be very from specified, depending on activity level of server. The reason behind is that for each WAITFOR statement, SQL Server assigns separate thread, and if server is busy then thread may not be scheduled immediately. In other case if SQL Server is having thread starvation, it can randomly stop WAITFOR thread's execution and release resources. If we need more perfection in delay, then we can use external applications like SSIS.
Cursors cannot be opened on WAITFOR statements. Also, Views cannot use WAITFOR statements. When using WAITFOR TIME option, we should pass time type variable to it. If we are passing datetime type variable to it, then it'll only considers time part of date.