WAITFOR

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'
GO

Here is the result set:

A total time of 000 hours, 00 minutes, and 10 seconds, has elapsed! Your time is up.

See Also

Control-of-Flow Language

datetime and smalldatetime

sp_who