The Alpha and the Omega. The BEGIN and the END.

2

October 4, 2017 by Kenneth Fisher

The BEGIN/END block is a fairly standard thing if you’ve ever done any coding, but it never hurts to do a little review. Not to mention that there are always people just starting to learn about a subject.

So what is a BEGIN/END block? They are boundaries that define a group of SQL statements.

BEGIN
   Put your SQL Code here
END

But what do they do?!?

Just that, define a block of code. They are not a transaction, they do not affect the order the statements run. So what use are they?

Well, let’s say, for example, that you have an IF statement:

IF 1=0
	PRINT 'a'
	PRINT 'b'

As you may very well already know, b is going to be printed. Now this isn’t true for all languages. In Python for example formatting actually affects how the code runs. In T-SQL, however, white space doesn’t do anything. In addition, statements like the IF..ELSE statement, and the WHILE statement only affect the single line of code after them.

Yep. Just one single line of code.

You might be asking, How exactly does that work?, I have to be able to run a bunch of code after an IF. In fact, I’ve seen it done before. And yes. You have. Sort of.

What you’ve actually seen is the IF, ELSE or WHERE running just one single line of code. The BEGIN statement. The BEGIN then tells SQL to run everything until the END of the block.

Last but not least you can nest them. Which is great until the end of your code looks something like this.

													END
												END
											END
										END
									END
								END
							END
						END
					END
				END
			END
		END
	END
END

Which is yet again a great reason for careful formating.

So in the end, the trick here is to remember that your IF, WHILE, etc only execute one statement. So if you want to do more than that you need the BEGIN and the END.

2 thoughts on “The Alpha and the Omega. The BEGIN and the END.

  1. Joe Celko says:

    It’s worth mentioning that in ANSI/ISO Standard SQL, we have “BEGIN [ATOMIC] .. END” in which the keyword atomic means that the block of text is to be treated as a single unit, essentially a transaction. Without it, the begin-end block is simply a sequence of statements and it can be interrupted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,151 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: