SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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.


Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, T-SQL

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...