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