WAITFOR clause in SQL Server 2008
In this article I describe about WAITFOR clause. WAITFOR statement used to delay execution of SQL command for a specified period of time.
The WAITFOR clause is used to delay the execution of batch, stored procedure or a T-SQL command for a specific period of time. This command is very useful. Suppose I want to restore the database to backup server then I use WAITFOR command. While executing the WAITFOR statement, no other request could under the same transaction. Time delay could me more then specified time because if server is busy then the thread may not be immediately scheduled.
WAITFOR parameter uses two parameters
- DELAY (Execute after specified delay)
- TIME (Executes at specified time).
Create simple stored procedure
create procedure sp_wait
select * from emp
This stored procedure will execute immediately. Now to execute stored procedure after a delay of 30 seconds.
WAITFOR Delay in executing stored procedure
--Delay for 30 seconds
waitfor delay '00:00:30'
Now execute preceding query. This stored procedure will execute after 30 seconds.
WAITFOR Time in executing stored procedure
--delay till 4:05 PM
waitfor time '16:05'
Now execute preceding statement. This query will execute the stored procedure at 04.05PM. The must be given in 24 hour format.