Create delays in SQL Server processes to mimic user input

By: Greg Robidoux   |   Updated: 2008-01-30   |   Comments (1)   |   Related: > Testing

Problem

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?

Solution

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 Steps

• Next time you have the need to create delays in your processing don't forget these different approaches

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips