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.
  • 5136

Introduction

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

  1. DELAY (Execute after specified delay)
  2.  TIME (Executes at specified time).

Example

Create simple stored procedure

create procedure sp_wait
as
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
begin
waitfor delay '00:00:30'
execute sp_wait
end

Now execute preceding query. This stored procedure will execute after 30 seconds.

WAITFOR Time in executing stored procedure

--delay till 4:05 PM
begin
waitfor time '16:05'
execute sp_wait
end

Now execute preceding statement. This query will execute the stored procedure at 04.05PM. The must be given in 24 hour format.

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.