Good morning guys! Today marks the end of my SQL transaction execution flow blog posts, and I'm finishing off with BEGIN...END statements (how appropriate!).

What is a BEGIN...END statement?

A BEGIN...END statement encloses transactions so that the entire group can be executed. They are commonly found within Stored Procedures, User-Defined Functions and Triggers, as you probably saw in my series of blogs on Stored Procedures. I have used BEGIN...END numerous times in the work place and it is probably one of the more commonly used control flow statements in SQL. 

Although it is possible to add a BEGIN...END statement around any series of transaction statements (and nest them), there are some circumstances where you may not necessarily need a BEGIN...END statement such as when there is only one statement to execute. However it is good practice to include a BEGIN...END statement anyway as not only does it improve code readability, but it can make it easier to update and expand on in the future.

An Example of a BEGIN... END statement

The code below shows an example of a BEGIN...END statement, whereby an INSERT statement is then followed up by a related SELECT statement: 

An Example of a nested BEGIN... END statement

The code below shows an example of nested BEGIN...END statements (taken from my trigger) whereby a new statement is run :

Conclusion to Series 

And that is pretty much the basics of flow control statements in SQL! As you can tell, there are many different methods to achieve the same goal (a term which crops up very often as a developer) so it is imperative that when selecting the right statements to use, always consider the following: 

• Code readability - How easy it is to read the transaction code and follow it through?
• Code accessibility - Is all code accessible at execution time? 
• Code re-usability - Is code concise with no repetition?  

As A Final Word...

Thank you for reading my latest blog post. If you have any questions, comments or feedback please don't hesitate to leave a comment below in the comment box and I will get back to you as soon as I can. Alternatively, please like and comment on my SQL Genius Facebook Page.

Following on from my conclusion, tomorrow's blog post is going to feature important coding practices such as code readability, accessibility and re-usability (as mentioned above) as it is paramount to the success of a web development project! (Something which I have definitely discovered and learnt from only too well in the work place). Stay tuned!

Good morning guys! 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 GOTO statement, including the good, the bad and the ugly! My aim at the end of this blog post is to highlight both the positives and the negatives so that you can make an informed decision on whether your code will benefit from the use of the GOTO statement or not.

What is a GOTO Statement?

A GOTO statement changes the execution flow of your transactions by explicitly stating where the flow should restart using labels. Any code in the block after the GOTO statement will be ignored and the flow will "jump" to where the label has been positioned before continuing.

Example of a GOTO Statement

Below is an example of a very simple GOTO statement (the idea taken from the Microsoft site, so thanks Microsoft!):

The output from the above query is: 

As you can see, once the counter reaches 8, the GOTO SectionOne changes the flow of the execution to the SectionOne block. At the end of that section, there is another GOTO statement which points to the SectionThree block, completely skipping SectionTwo. Usually, SQL code works in a procedural way so the flow of the code usually continues downwards. However, GOTO statements provide the flexibility to change this to fit the needs of the procedure.  

The Positives

There are several positive reasons to use GOTO statements within your queries:

• GOTO statements can be used anywhere within a statement block, making it very versatile. 
• GOTO statements are very easy to declare and use, which makes them a favourite among less experienced developers (though as you will discover in the next section, ease of implementation is only one side of the coin and certainly doesn't mean it's a perfect solution!). 
• GOTO permissions can be used by any valid user on the database server.
• GOTO statements can be nested within each other, meaning that you can "jump" to literally any position within your procedures and can control the flow as much as possible. 

The Negatives

As with everything, there are also negatives to match with those positives and this section will explain why you should avoid using GOTO statements.

Edsger Dijkstra (for those of you who don't know, he was a very prestigious Dutch computer scientist who won a Turing award for his contributions to the development of programming languages) has stated in his publication "Goto Considered Harmful" that if you use GOTO statements, it can potentially make your code unreadable, unreliable and difficult to debug when something goes wrong.

Dijkstra is trying to emphasise the fact that GOTO statements are so versatile that they could also work as a negative as you can literally place GOTO statements anywhere in your code. Some developers use this as an excuse to do exactly that; place them absolutely everywhere. This can result in unreachable code that would be difficult to debug and follow through as there are so many "jumps" altering execution flow. Some resources on the Internet I have found actually say that GOTO statements should be banned all together as there are alternative control flow methods that may be more difficult to implement, but overall will promote code readability and will prevent unnecessarily overcomplicated transactions from being produced.

Conclusion

In conclusion to this post, I think that it's advisable to avoid using GOTO statements as much as possible. If you find yourself in a situation where you have to use it, make sure that you think and plan ahead very carefully in order to promote code reliability and usability. If there is an alternative method you can use, don't cut corners and make sure you do it properly the first time around! It's very important to make your code as readable as possible so always keep this in mind (I will post a blog about why this is so important at the end of the week!).

What Next...

Tomorrow, I will be writing about BEGIN ... END statements, something which I have used a fair amount in my work experience but I haven't documented it yet in my blog yet. This will mark the end of my Control-Of-Flow series. Keep your eyes peeled!

Thank you 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. Thanks! :)

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!

Good morning guys! Hope you all had a lovely weekend!

Today's SQL blog post is going to discuss WHILE Statements in SQL and how they can be used as a method of looping through and controlling the flow of your transaction statements.

What is a WHILE loop?

A WHILE loop is a function in programming that allows a statement to be repeated based on specific conditions. The code inside the body of the loop will be repeated continuously while the condition is being met however once the condition is no longer met, the transactional flow will break out of the loop and the rest of the code after the WHILE loop has ended will be executed.

An example of a simple WHILE loop in SQL is displayed below:

To give a better understanding on how WHILE loops work, the diagram below shows how the above code would work. The WHILE loop would begin and check variable x to see if the value is less than 10. If it is, then the Boolean check will return TRUE and 1 will be added to x. This will keep occurring until x is no longer less than 10. When this is the case, the Boolean check will return FALSE and the WHILE loop will end, passing the code flow to the next statement in the transaction block (if there is any code afterwards).

BREAK and CONTINUE 

BREAK and CONTINUE are 2 arguments that can be used with a WHILE loop to further control the flow of the loop:

• BREAK - causes a break out of the loop and any code after the WHILE loop has ended is then executed. 
• CONTINUE - causes the loop to restart, ignoring any other code after the CONTINUE keyword. 

WHILE loops can also be nested. If this is the case, then all statements will be executed within the inner loop first before control is passed to the outer loop. Using a BREAK statement in a nested WHILE loop will break out of the innermost loop and then transfer control to the outer loop.

Examples of WHILE Loops

You can use WHILE loops in a variety of different situations. The example below shows a SELECT statement used as the condition of the loop (which must be enclosed in parentheses) and an IF statement within the WHILE loop to control the flow:

WHILE statements can also be used in database cursors, where @@FETCH STATUS is used to control the cursor within the WHILE loop. For more information on cursors and @@FETCH STATUS, please read my blog post on Cursors in Stored Procedures (which can be found here).

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.

Tomorrow, I will be writing about using WAITFOR in order to control the flow of your transaction statements. Stay tuned!

Many thanks again for reading! 

Good morning everyone! Today's short blog post (as promised) is going to be about IF ... ELSE statements. IF ... ELSE statements are conditional, so their result is dependent on whether the IF part of the statement has been satisfied. If not, then control passes to the corresponding ELSE statement. Batches of these statements can be used, however make sure you limit how many are nested as it can have a negative impact on speed and query performance if you're not careful.

Personally, I have come across IF ... ELSE statements regularly in projects at work and have used them more than CASE statements - mainly because although their functionality is similar, IF ... ELSE statements are used to control the flow of transactions within a Stored Procedure, whereas CASE statements are not.

Simple IF ... ELSE Statement

An example of a simple IF ... ELSE statement is shown below. The variable Price is declared and given a value of £9.99. If the value of Price is less than £10.00, then display a message to the user to state that this is a sale item. If not, then display a message to the user to state that it is actually a full price item.

IF ... ELSE Statement with SELECT condition

An example of an IF ... ELSE statement that relies on a result from a SELECT statement is shown below. If there are more than 2 assets in the database that are located in "Basildon", then display a message to the user to state that there are more than 2 assets in Basildon. If this is not the case however, display a message to the user stating that there are 2 or less assets in Basildon. 

Nested IF... ELSE Statement

An example of a nested IF ... ELSE statement is shown below. This is taken from the example above and expanded so if there are fewer than 5 assets in Basildon, perform another check to see if there are any assets in Basildon at all. When nesting IF statements, you will need to surround each individual nested statement with a BEGIN ... END clause. 

Overall

IF...ELSE statements can be flexible and used in many situations with a combination of different control flow statements, which I will explain in more detail (with examples) during the coming days.
  

What Next...

Next week, I will be writing more about the WHILE statement within SQL. Stay tuned! 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. Thanks for reading my blog post and thanks to my blog followers for all of your support on my journey! Have a great weekend everyone!