T-SQL: The WAITFOR Statement

Excerpt by Don Kiely | April 15, 2013

Syntax that controls the flow of statement execution is an essential feature of any procedural programming language. The core SQL standard implements set-based operations, and only introduced procedural features like flow control starting in the 1999 version. Transact-SQL extends the standard to support the usual flow control mechanisms for conditional branching and looping of code execution. This article discusses the WAITFOR statement.

The WAITFOR statement

The WAITFOR statement sets the SQL Server query processor to a holding pattern until either a specific amount of time elapses or an absolute time occurs:

WAITFOR {DELAY 'time' | TIME 'time'}

This is especially useful during testing, if you need to simulate a long-running query. The following examples show how to use WAITFOR. The first statement pauses for 10 seconds, and the second until noon. Keep in mind that your database connection will be considered blocked until the WAITFOR completes, so execute the second example with care!

--Pause for ten seconds
WAITFOR DELAY '000:00:10';
PRINT 'Done';
--Pause until a certain time
WAITFOR TIME '12:00:00';
PRINT 'It is noon';

You can keep an eye on the duration of the pause in the first query in the lower right corner of the query editor window, as shown in the Figure below. You might not see every one of the 10 seconds of the count because of Windows' preemptive multitasking, but it will give you an idea of how long the query is taking to execute. At 10 seconds, it should complete.

The query duration counter in the query editor window

If you try to run the second query and it doesn't happen to be shortly before noon, it will be a good time to know about the square red toolbar button in Management Studio, which allows you to cancel an executing query as shown in the Figure below.

Click the square red button to cancel an executing query

WAITFOR isn't a statement that you'll want to use very often-it runs counter to our usual obsession with making code run faster, not slowing it down! But sometimes you have to wait for other external things to happen, and in those cases a strategically placed WAITFOR is just what you need.

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.

Don Kiely

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

This course excerpt was originally posted April 15, 2013 from the online courseware SQL Server 2012, Part 7 of 9: Introduction to T-SQL by Don Kiely