Most programming languages have a command that allows a process to sleep (or wait) before continuing. SQL Server is no exception. Since SQL Server 2005 there has support for a sleep command through the use of the WAITFOR command.
Sleep For A Specific Amount Of Time
The WAITFOR DELAY command will allow you to specify the amount of time that you would like to pass before continuing on with the code. You can specify the amount of time down to the milliseconds (’00:00:00:001′) if you need it more accurate.
|
1 2 3 4 |
-- Pause for 5 seconds WAITFOR DELAY '00:00:05' SELECT 'Done' AS QueryStatus |
Sleep Until A Specific Time
The WAITFOR TIME command allows you to specify the exact time to wait for until before continuing the code. The following command will wait until 5:40 PM before continuing to the next line of code. You can specify seconds as ‘5:40:35 PM’.
|
1 2 3 4 |
-- Pause until 5:40 WAITFOR TIME '5:40 PM' SELECT 'Done' AS QueryStatus |
You can also use a DATETIME variable to specify the time to wait until. If you do use a DATETIME datatype, the date portion will be ignored and only the time portion will be evaluated.
|
1 2 3 4 5 6 7 |
DECLARE @d DATETIME SET @d = DATEADD(SECOND, 5, GETDATE()) -- Pause until 5 seconds in the future WAITFOR TIME @d SELECT 'Done' AS QueryStatus |
MSDN reference: http://msdn.microsoft.com/en-us/library/ms187331.aspx