BEGIN... END Statements

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!