WAITFOR:

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.:

Blocks unitil:

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.

Syntax:

WAITFOR
{
    DELAY 'time_to_pass'
  | TIME 'time_to_execute'
  | [ ( receive_statement ) | (
get_conversation_group_statement ) ] 
    [ ,
TIMEOUT timeout ]
}

Arguments:

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.

Example:

--Query1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ   
BEGIN TRAN 
SELECT * FROM Employee 
WAITFOR DELAY '00:00:10' 
SELECT * FROM Employee 
ROLLBACK 
 
--Query2
INSERT INTO Employee(EmployeeName,Country,PrimaryLanguage,CompanyPlant,ManagerId)
 
VALUES ('Anushka', ‘England’,'English','London',5) 

Result: In query 1 two select statements, first select statement before (WAITFOR) return deferent result and second select return different result, reason query 2 executed in wait for passed time, query 2 insert statement executed.