WAITFOR (T-SQL)

Specifies a time, time interval, or event that triggers the execution of a statement block, stored procedure, or transaction.

Syntax

WAITFOR {DELAY 'time' | TIME 'time'}

Arguments

DELAY: Instructs Microsoft® SQL Server™ to wait until the specified amount of time has passed, up to a maximum of 24 hours.
'time': Is the amount of time to wait. time 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 portion of the datetime value is not allowed.
TIME: Instructs SQL Server to wait until the specified time.

Remarks

After executing the WAITFOR statement, you cannot use your connection to SQL Server until the time or event that you specified occurs.

To see the active and waiting processes, use sp_who.

Examples

A. Use WAITFOR TIME

This example executes the stored procedure update_all_stats at 10:20 P.M.

BEGIN

WAITFOR TIME '22:20'

EXECUTE update_all_stats

END

For more information about using this procedure to update all statistics for a database, see the examples in UPDATE STATISTICS.

B. Use WAITFOR DELAY

This example shows how a local variable can be used with the WAITFOR DELAY option. A stored procedure is created to wait for a variable amount of time and then returns information to the user as to the number of hours, minutes, and seconds that have elapsed.

CREATE PROCEDURE time_delay @@DELAYLENGTH char(9)

AS DECLARE @@RETURNINFO varchar(255)

BEGIN

WAITFOR DELAY @@DELAYLENGTH

SELECT @@RETURNINFO = 'A total time of ' +

SUBSTRING(@@DELAYLENGTH, 1, 3) +

' hours, ' +

SUBSTRING(@@DELAYLENGTH, 5, 2) +

' minutes, and ' +

SUBSTRING(@@DELAYLENGTH, 8, 2) +

' seconds, ' +

'has elapsed! Your time is up.'

PRINT @@RETURNINFO

END

GO

-- This next statement executes the time_delay procedure.

EXEC time_delay '000:00:10'