GOTO Statements

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! :)