SQL SERVER – Time Delay While Running T-SQL Query – WAITFOR Introduction

Today we will look at one very small but interesting feature of SQL Server. Please note that this is not much known feature of SQL Server. In SQL Server sometime there are requirement when T-SQL script has to wait for some time before executing next statement. It is quite common that developers depends on application to take over this delay issue. However, SQL Server itself has very strong time management function of WAITFOR. Let us see two usage of WAITFOR clause.

Official explanation of WAITFOR clause from Book Online is “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.”

Option 1 : Waitfor Delay in executing T-SQL
T-SQL runs after particular delay is completed.

SELECT GETDATE() CurrentTime
WAITFOR DELAY '00:00:05' ---- 5 Second Delay
SELECT GETDATE() CurrentTime

Option 2 : Waitfor Time in executing T-SQL
T-SQL runs after particular time has arrived.

DECLARE @MyDateTime DATETIME
/* Add 5 seconds to current time so
system waits for 5 seconds*/
SET @MyDateTime = DATEADD(s,5,GETDATE())
SELECT GETDATE() CurrentTime
WAITFOR TIME @MyDateTime
SELECT GETDATE() CurrentTime

Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)