WAITFOR (Transact-SQL)
Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row.
- DELAY
-
Is the specified period of time that must pass, up to a maximum of 24 hours, before execution of a batch, stored procedure, or transaction proceeds.
- ' time_to_pass '
-
Is the period of time to wait. time_to_pass can be specified in one of the acceptable formats for datetime data, or it can be specified as a local variable. Dates cannot be specified; therefore, the date part of the datetime value is not allowed.
- TIME
-
Is the specified time when the batch, stored procedure, or transaction runs.
- ' time_to_execute '
-
Is the time at which the WAITFOR statement finishes. time_to_execute can be specified in one of the acceptable formats for datetime data, or it can be specified as a local variable. Dates cannot be specified; therefore, the date part of the datetime value is not allowed.
- receive_statement
-
Is a valid RECEIVE statement.
Important
WAITFOR with a receive_statement is applicable only to Service Broker messages. For more information, see RECEIVE (Transact-SQL).
- get_conversation_group_statement
-
Is a valid GET CONVERSATION GROUP statement.
Important
WAITFOR with a get_conversation_group_statement is applicable only to Service Broker messages. For more information, see GET CONVERSATION GROUP (Transact-SQL).
- TIMEOUT timeout
-
Specifies the period of time, in milliseconds, to wait for a message to arrive on the queue.
Important
Specifying WAITFOR with TIMEOUT is applicable only to Service Broker messages. For more information, see RECEIVE (Transact-SQL) and GET CONVERSATION GROUP (Transact-SQL).
While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction.
The actual time delay may vary from the time specified in time_to_pass, time_to_execute, or timeout and depends on the activity level of the server. The time counter starts when the thread associated with the WAITFOR statement is scheduled. If the server is busy, the thread may not be immediately scheduled; therefore, the time delay may be longer than the specified time.
WAITFOR does not change the semantics of a query. If a query cannot return any rows, WAITFOR will wait forever or until TIMEOUT is reached, if specified.
Cursors cannot be opened on WAITFOR statements.
Views cannot be defined on WAITFOR statements.
When the query exceeds the querywait option, the WAITFOR statement argument can complete without running. For more information about the configuration option, see query wait Option. To see the active and waiting processes, use sp_who.
Each WAITFOR statement has a thread associated with it. If many WAITFOR statements are specified on the same server, many threads can be tied up waiting for these statements to run. SQL Server monitors the number of threads associated with WAITFOR statements, and randomly selects some of these threads to exit if the server starts to experience thread starvation.
You can create a deadlock by running a query with WAITFOR within a transaction that also holds locks preventing changes to the rowset that the WAITFOR statement is trying to access. SQL Server identifies these scenarios and returns an empty result set if the chance of such a deadlock exists.
A. Using WAITFOR TIME
The following example executes the stored procedure sp_update_job at 10:20 P.M. (22:20).
USE msdb;
EXECUTE sp_add_job @job_name = 'TestJob';
BEGIN
WAITFOR TIME '22:20';
EXECUTE sp_update_job @job_name = 'TestJob',
@new_name = 'UpdatedJob';
END;
GO
B. Using WAITFOR DELAY
The following example executes the stored procedure after a two-hour delay.
BEGIN
WAITFOR DELAY '02:00';
EXECUTE sp_helpdb;
END;
GO
C. Using WAITFOR DELAY with a local variable
The following example shows how a local variable can be used with the WAITFOR DELAY option. A stored procedure is created to wait for a variable period of time and then returns information to the user as to the number of hours, minutes, and seconds that have elapsed.
USE AdventureWorks;
GO
IF OBJECT_ID('dbo.TimeDelay_hh_mm_ss','P') IS NOT NULL
DROP PROCEDURE dbo.TimeDelay_hh_mm_ss;
GO
CREATE PROCEDURE dbo.TimeDelay_hh_mm_ss
(
@DelayLength char(8)= '00:00:00'
)
AS
DECLARE @ReturnInfo varchar(255)
IF ISDATE('2000-01-01 ' + @DelayLength + '.000') = 0
BEGIN
SELECT @ReturnInfo = 'Invalid time ' + @DelayLength
+ ',hh:mm:ss, submitted.';
-- This PRINT statement is for testing, not use in production.
PRINT @ReturnInfo
RETURN(1)
END
BEGIN
WAITFOR DELAY @DelayLength
SELECT @ReturnInfo = 'A total time of ' + @DelayLength + ',
hh:mm:ss, has elapsed! Your time is up.'
-- This PRINT statement is for testing, not use in production.
PRINT @ReturnInfo;
END;
GO
/* This statement executes the dbo.TimeDelay_hh_mm_ss procedure. */
EXEC TimeDelay_hh_mm_ss '00:00:10';
GO
Here is the result set.
A total time of 00:00:10, in hh:mm:ss, has elapsed. Your time is up.
WAITFOR uses datetime data type to specify how long to wait. The datetime data type has maximum precision of 3.33ms. So if you specify, for example,
WAITFOR DELAY '00:00:00.001'
then the statement would actually return without waiting for 1ms.
In general you should not depend on WAITFOR to wait for very short amount of time less than 15ms. For example, execute following statements:
declare @start datetime, @end datetime, @i int
set @i = 0
while @i < 100
begin
set @start =GETDATE()
waitfordelay'00:00:00.014'--somehow any values less than 15ms does not work
set @end =GETDATE()
IF @start = @end
RAISERROR('waitfor didn not wait!!',1,1) WITH NOWAIT
set @i = @i + 1
end
In my tests there is about 17% chance that WAITFOR doesn't wait and GetDate() call returns same value as previous call if you set the delay to 14ms. If you increment delay to 15ms you may consistently see that WAITFOR is actually waiting it out. Please not that this is an experimental results and not an official statement.
