Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Create delays in SQL Server processes to mimic user input


By:   |   Updated: 2008-01-30   |   Comments (1)   |   Related: More > 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


Last Updated: 2008-01-30


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, July 01, 2012 - 6:25:08 PM - Byron Back To Top

Thanks Greg, sometimes it is the simple things that make the biggest difference. This had escaped my notice until now...
-byron


Learn more about SQL Server tools