The WAITFOR Statements In SQL Server

The WAITFOR statement is used to stop the execution of the current transaction/Stored procedure until delay time or specified time.

How to Use WAITFOR?

It can be used with different combinations like DELAY, TIME, or till for service brokers to receive messages. If multiple WAITFOR statements are defined on the identical server, multiple lines can be connected up waiting for these statements to execute. SQL Server observes the number of WAITFOR statement lines and erratically specifies some of these lines to exit if the server begins to encounter line starvation.

1. DELAY is used if we want to stop execution for a certain amount of time. Its syntax is as below.

WAITFOR DELAY 'delayTimeAmount'

If I want to delay execution for 5 seconds, then it can be written as below.

WAITFOR DELAY '00:00:05'

Here delay time is specified HH:MM:SS format.

2. TIME option is used in case we can wait until a particular time. For example, if I want to wait for execution till 13:15 then I’ll go for the TIME option. Its syntax is as below.

WAITFOR TIME 'time'

3. When we are working with the service broker and we want to wait until the message has arrived, then we can use as specified in the below syntax.

WAITFOR (RECEIVE 'receive query')

If we don’t know, how much time WAITFOR takes and we will want to set a timeout we can do this by specifying TIMEOUT after the WAITFOR statement.

In the Wait for query, the important thing to remember is that if a query failed to give any rows, then WAITFOR will remain permanently or until TIMEOUT is attained if defined. In SQL, cursors and view can’t be defined on WAITFOR statements. When the query surpasses the query wait alternative, the WAITFOR statement can terminate without executing. It also stops the implementation of a set, stored procedure, or transaction until either a defined period or time interval proceeds, or a fixed statement alters or produces at least one row.

Code Example

Find the example here:

Final Note

The actual time to delay can vary from specified, depending on the activity level of the server. The reason behind this is that for each WAITFOR statement, SQL Server assigns a separate thread, and if the server is busy then the thread may not be scheduled immediately. In other cases, if SQL Server is having thread starvation, it can randomly stop WAITFOR thread’s execution and release resources. If we need more perfection in delay, then we can use external applications like SSIS.

Cursors cannot be opened on WAITFOR statements. Also, Views cannot use WAITFOR statements. When using the WAITFOR TIME option, we should pass the time type variable to it. If we are passing the DateTime type variable to it, then it’ll only consider the time part of the date.

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.