LearnNowOnline

Blog

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.

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.

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.

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