In this article we will show you how to use the WAITFOR statement in SQL Server. The WAITFOR statement is used to delay the execution of a T-SQL script, stored procedure, or transaction. Using the WAITFOR TIME statement, a job can be set to run at a specified time.

The WAITFOR DELAY statement can also be used to delay the running of a procedure. The main advantage of using the WAITFOR statement is that it helps order the running of jobs or stored procedures in the database. When the WAITFOR statement is used, all orders to be executed wait for the specified time or delay.

Syntax

WAITFOR
{
DELAY 'time_to_pass'
| TIME 'time_to_execute'
| [ ( receive_statement ) | ( get_conversation_group_statement ) ]
[ , TIMEOUT timeout ]
}

WAITFOR TIME example

In the first example of this article, we will show how to use the WAITFOR TIME statement. The first step is to create a Test6 table, then wait a certain time to execute the renaming of the table from Test6 to Test7. After renaming the table, insert into the table is automatically run using the new name.

USE model
GO
CREATE TABLE Test6(id int);
BEGIN
WAITFOR TIME '21:38';
EXECUTE sp_rename 'Test6', 'Test7';
END;
GO
INSERT INTO Test7(id) VALUES (1);
GO

WAITFOR DELAY example

In the second example, we will show how to use the WAITFOR DELAY statement. Before inserting into the Test7 table, the WAITFOR DELAY statement sets the execution to be delayed by 5 seconds from the run date.

USE model
GO
WAITFOR DELAY '00:00:05'
INSERT INTO Test7(id) VALUES (2);
SELECT * FROM Test7;
GO