WAITFOR Statements

Good morning everyone! As promised, today's blog post is a continuation of my transactional flow series - discussing ways of controlling the execution of your SQL Transactions. This blog post will cover the WAITFOR statement, what it is used for and how it can be used in your SQL transactions.

What is a WAITFOR statement?

WAITFOR is a statement in SQL that blocks the execution of another statement or a collection of statements until one of the following conditions is met:

• An amount of time declared by the user has passed.
• A specific time declared by the user has been reached. 
• A RECEIVE statement returns at least one message from a Service Broker Queue.

While running a WAITFOR statement, no other requests can be made to the same transaction as it has been isolated until it meets the condition to continue. This is used quite often, and is especially useful if you wish to run a stored procedure at a particular time of day.

There are a few things that you will need to be aware of:

• If there is a lot of activity on the database server, then there is a chance that the WAITFOR statement will execute later than scheduled. Always make sure that, if the timing is vital, that the database server you are running the transaction on is relatively free. 
• You cannot open cursors or define views in a WAITFOR statement. 
• A deadlock situation may occur if locks preventing changes to a row are in place and the WAITFOR statement is trying to access it. 
• If, for whatever reason, a query can't return any rows or becomes stuck in an infinite loop, it will just continue to wait unless you use a TIMEOUT clause (in milliseconds).
  

WAITFOR TIME Example 

The following example shows a stored procedure that will be executed at 10:30pm (once this query has been run). This is also an example of a nested execution, whereby you can execute one procedure and then wait to execute another:

WAITFOR DELAY Example

The following example shows a stored procedure that will be executed in 1 hour's time (once this query has been executed):

RECEIVE 

You can also use a RECEIVE clause with a WAITFOR statement which will wait to retrieve a message from a Service Broker Queue (a mechanism for holding incoming messages). Please note that there will be a future blog post to explain how Service Broker Queues work in more detail.

An simple example of this implemented is shown below:

 

Overall

WAITFOR statements are incredibly useful for executing your transactions exactly when you want them to be executed. They are also very flexible as they can be nested and used with other transactional flow statements. 

What Next...

Thanks for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there. I really appreciate everyone's support!

Tomorrow, I will be writing about GOTO statements (the good, the bad and the ugly!) So stay tuned!

Many thanks again for reading!