Create delays in SQL Server processes to mimic user input
By: Greg Robidoux | Updated: 2008-01-30 | Comments (1) | Related: More > Testing
Sometimes when running processes there may be the need to create delays before the next step runs. This could be for processes that are run externally and therefore you have no control over when that process finishes, the need to mimic a delay in user response if you are doing testing or maybe you are collecting data at various intervals and want to delay the next collection time. These are just a few examples of the need to create a delay, so what approaches exist?
As we have seen with other tips that have been written, a lot of these functions and features are built right into SQL Server and the need to create a delay is no exception. SQL Server offers the WAITFOR statement that allows you to wait for a specified amount of time or until a specified time to process the next step in the sequence of events.
The command can be simply written as follows:
-- this will delay the processing for 30 seconds WAITFOR DELAY '00:00:30' -- this will delay the processing for 1 hour, 15 minutes and 30 seconds WAITFOR DELAY '01:15:30' -- this will delay the processing until 8:00am WAITFOR TIME '08:00'
With this you could run the following code to get the number of connections on the server, delay the process and then get the data again. The one thing to note here is that results are not shown until the process completes. Even though this is the case this, these statements are not run as one transaction.
select getdate(), count(*) as 'NumberOfProcesses' from sys.sysprocesses; WAITFOR DELAY '00:00:15' select getdate(), count(*) as 'NumberOfProcesses' from sys.sysprocesses;
If you want to get data back as soon as it completes you could do the following instead:
select getdate(), count(*) as 'NumberOfProcesses' from sys.sysprocesses; GO WAITFOR DELAY '00:00:15' select getdate(), count(*) as 'NumberOfProcesses' from sys.sysprocesses; GO
To mimic user input for testing you could issue something like the following:
INSERT INTO table1 (FirstName) VALUES ('Brian'); WAITFOR DELAY '00:00:05' SELECT * FROM table1 WHERE FirstName = 'Brian'; WAITFOR DELAY '00:00:05' INSERT INTO table1 (FirstName) VALUES ('Tim'); WAITFOR DELAY '00:00:15' INSERT INTO table1 (FirstName) VALUES ('Steve'); WAITFOR DELAY '00:00:05' SELECT * FROM table1 WHERE FirstName IN ('Brian','Tim'); WAITFOR DELAY '00:00:15' INSERT INTO table1 (FirstName) VALUES ('Andy');
Although using the WAITFOR statement is the simplest way to do this, another approach could be to use a WHILE loop.
This first example uses a WHILE loop and quits after the counter hits a certain number. The problem with this approach is that some systems will run faster than others, so there is no way to get an exact time delay from system to system.
DECLARE @counter int SET @counter = 0 WHILE @counter < 10000000 SET @counter = @counter + 1
This next example checks the current date/time against a date and time that you set. One advantage to this approach is that you can specify a date and time, where with the WAITFOR command you can only specify a time. Setting of the dummy variable is only done because there needs to be some valid statement after the WHILE check.
DECLARE @startTime datetime DECLARE @dummy int SET @startTime = '2008-01-29 19:53:00' WHILE getdate() < @startTime SET @dummy = 0
This next example waits for a specified amount of seconds using the DATEADD function and comparing it to the current date and time. This could be changed to minutes, days, etc.... In addition, this also uses the dummy variable because a valid statement is required after the WHILE command.
DECLARE @startTime datetime DECLARE @delay int DECLARE @dummy int SET @delay = 30 SET @startTime = DATEADD(s, 30, getdate()) WHILE getdate() < @startTime SET @dummy = 0
As we have shown, here are a few ways that you can create delays in your processing. Another approach could be to just use scheduled jobs if you have long delays between processing.
- Next time you have the need to create delays in your processing don't forget these different approaches
Last Updated: 2008-01-30
About the author
View all my tips