1. About Execution Delay

In some case, we may need to pause the execution for certain period say for example 5 seconds. And in some situation, we may need to pause the execution till some time of the day elapses. In this example, we will see how to introduce execution delay in SQL Server T-SQL Script using the WaitFor clause.

2. WaitFor Clause

The WaitFor class provides two options to introduce execution delays. They are:

1. Delay
2. Time

The Delay option is to specify the wait time. Say for example, WaitFor Delay 00:00:03 states that the SQL Server Execution engine will sleep for 3 seconds and then wakeup to execute remaining statements. Time option specifies the time of the day and SQL Server Engines waits for that specific time to elapse before executing the next statement. Now, let see an example for both the options.

3. Execution Delay – WaitFor Delay Option

Now let us see how the WaitFor delay option works within a T-SQL. Have a look at the below example:

Explanation

1. Here, we print a message so that it appears in the message tab of the SSMS.
2. This snippet uses the ‘WaitFor Delay’ option to halt the execution. We can specify how long to wait in time format. In our example, we pause the execution for 4 seconds.
3. This is a simple select statement which picks records from Authors table of the pubs database.

When you run the above statements in SSMS studio, you will see a message ‘Calling Wait…’ and then a 4 second delay in the execution followed by the select query result.

4. Execution Delay – WaitFor Time Option

Now we will look at another example which will halt the execution till the clock get past certain time of the day. Here, we use the ‘WaitFor Time’ option and below is the example.

Explanation

1. Like our previous example, here we print message which appears in the message tab.
2. The option ‘WaitFor Time’ will halt the execution until time specified. In our example, the execution stops till the time reached 3:17:07 PM
3. This is a sample select statement which prints the result when clock ticks – 15:17:05

To experiment the example, change the time of the day at step number two then watch the clock and SSMS output window.

5. Code Reference

6. Summary

You can use this option is a SQL Server batch file or in a stored procedure or in a function. However, care should be provided as these options halts the execution. Make sure proper wakeup time is provided.

• Singing An Assembly via CommandLine & VS2017
• Lambda Expression & Functional Interface

Categories: SQL Server

This site uses Akismet to reduce spam. Learn how your comment data is processed.