Oracle PL/SQL WAITFOR T-SQL · Control-of-Flow · WAITFOR

T-SQL WAITFOR Statement

Master T-SQL WAITFOR — pause execution with WAITFOR DELAY (for a duration) or WAITFOR TIME (until a clock time). Practical batch-throttling examples and critical warnings about connection/lock retention.

What is WAITFOR?

WAITFOR pauses execution of a batch, stored procedure, or transaction. The pause can be a duration ("wait 30 seconds") or a specific time of day ("wait until 14:00"). It's useful for delays, simple scheduling, polling loops, and demonstration scripts.

⚠️ WAITFOR keeps the connection occupied. While paused, the worker thread, locks, and any open transaction stay allocated to your session. Don't put it on any code path that a real user is waiting on, and don't use it inside long-running transactions — you'll block other queries.

Two Forms

Form	Effect	Format
`WAITFOR DELAY 'time'`	Pause for the given duration	`'hh:mm:ss'`
`WAITFOR TIME 'time'`	Pause until that wall-clock time arrives today	`'hh:mm:ss'` (24-hour)

(There's also WAITFOR (RECEIVE ...) for waiting on Service Broker messages — advanced usage outside the scope of this page.)

WAITFOR DELAY

WAITFOR DELAY 'hh:mm:ss';

The maximum delay is 24 hours ('23:59:59'). For longer pauses you'd build them with WHILE.

Example 1 — Pause for 10 Seconds

T-SQL — Simple delay

PRINT 'Started at: ' + CONVERT(VARCHAR(8), SYSDATETIME(), 108);

-- Wait for 10 seconds
WAITFOR DELAY '00:00:10';

PRINT 'Resumed at: ' + CONVERT(VARCHAR(8), SYSDATETIME(), 108);

OUTPUT

Messages

Commands completed successfully.

Started at: 14:23:05

Resumed at: 14:23:15

WAITFOR TIME

WAITFOR TIME 'hh:mm:ss';

⚠️ TIME refers to today only. If the requested time has already passed today, behavior depends on the SQL Server version — you may get an immediate continue, an error, or in older versions a 24-hour wait. For safety, prefer WAITFOR DELAY with a calculated duration when you need cross-midnight reliability.

Example 2 — Pause Until a Specific Time

T-SQL — Wait until 12:00 PM

-- Wait until 12:00 PM (assuming the script started before noon)
WAITFOR TIME '12:00:00';

PRINT 'It is now noon. Resumed at: ' +
      CONVERT(VARCHAR(8), SYSDATETIME(), 108);

OUTPUT

Messages

Commands completed successfully.

It is now noon. Resumed at: 12:00:00

Real Scenario — Throttled Batch Processing

A practical use: insert several batches of orders with a deliberate pause between them — so a downstream system isn't overwhelmed.

Example 3 — Two Batches, 10s Apart

SQL — Setup

CREATE TABLE Orders (
    OrderID       INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName  NVARCHAR(100),
    OrderDate     DATETIME DEFAULT GETDATE(),
    Status        NVARCHAR(50) DEFAULT 'Pending'
);

T-SQL — Throttled batch loader

-- First batch
INSERT INTO Orders (CustomerName, Status)
VALUES
    ('John Doe',      'Processing'),
    ('Jane Smith',    'Processing'),
    ('Alice Johnson', 'Processing');

-- Pause for 10 seconds
WAITFOR DELAY '00:00:10';

-- Second batch
INSERT INTO Orders (CustomerName, Status)
VALUES
    ('Bob Brown',     'Processing'),
    ('Charlie Davis', 'Processing'),
    ('Emily White',   'Processing');

SELECT OrderID, CustomerName, OrderDate, Status FROM Orders;

OUTPUT

Results

(6 rows affected)

OrderID CustomerName OrderDate Status

─────── ────────────── ─────────────────────── ──────────

1 John Doe 2026-05-03 14:23:05.000 Processing

2 Jane Smith 2026-05-03 14:23:05.000 Processing

3 Alice Johnson 2026-05-03 14:23:05.000 Processing

4 Bob Brown 2026-05-03 14:23:15.000 Processing

5 Charlie Davis 2026-05-03 14:23:15.000 Processing

6 Emily White 2026-05-03 14:23:15.000 Processing

Notice the OrderDate column — the first three rows share one timestamp, and the second three are 10 seconds later. The WAITFOR DELAY created the gap.

Where WAITFOR Belongs (and Where It Doesn't)

Use case	WAITFOR appropriate?
Throttling batch jobs in a SQL Agent script	Yes
Demonstration / teaching scripts	Yes
Simple polling loop in maintenance code	Yes (with care)
Inside a transaction	No — locks are held while waiting
Inside a stored procedure called from a web/app server	No — ties up an active worker thread and the connection
"Sleep" inside a trigger	Definitely not — the calling DML waits, holding all its locks

Conclusion

WAITFOR DELAY 'hh:mm:ss' pauses for that duration; WAITFOR TIME 'hh:mm:ss' pauses until that time of day.
Maximum delay is 24 hours; for longer waits, build them with WHILE.
While paused, the connection's worker thread, locks, and open transaction stay allocated — never use WAITFOR on a request that a user is waiting on.
Best fits: throttled batch jobs, scheduled scripts, polling loops in maintenance code.
For cross-midnight scheduling, prefer SQL Server Agent over WAITFOR TIME.