Share via

  • Article

Using WAITFOR

The WAITFOR statement suspends the execution of a batch, stored procedure, or transaction until:

  • A specified time interval has passed.

  • A specified time of day is reached.

  • A specified RECEIVE statement modifies or returns at least one row to a Service Broker queue.

The actual time delay may vary from the time specified 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.

The WAITFOR statement is specified with one of the following clauses:

  • The DELAY keyword followed by a time_to_pass before completing the WAITFOR statement. The time to wait before completing the WAITFOR statement can be up to 24 hours.

    The following example uses the DELAY keyword to wait for two seconds before performing a SELECT statement:

    WAITFOR DELAY '00:00:02';
SELECT EmployeeID FROM AdventureWorks.HumanResources.Employee;

  • The TIME keyword followed by a time_to_execute, which specifies the time that the WAITFOR statement completes.

    The following example uses the TIME keyword to wait until 10 P.M. (22:00) to perform a check of the AdventureWorks database to make sure that all pages are correctly allocated and used:

    USE AdventureWorks;
GO
BEGIN
    WAITFOR TIME '22:00';
    DBCC CHECKALLOC;
END;
GO

  • A RECEIVE statement clause, which retrieves one or more messages from a Service Broker queue. When WAITFOR is specified with a RECEIVE statement, the statement waits for a message to arrive on the queue, if no messages are currently present.

  • The TIMEOUT keyword followed by a timeout specifies the length of time, in milliseconds, that Service Broker waits for a message to arrive on the queue. TIMEOUT can be specified in the RECEIVE statement or in the GET CONVERSATION GROUP statement.