Statement Blocks with BEGIN...END

The BEGIN and END keywords work together to group statements together. This is used in later constructs for loops and conditional statements, so although it may not make a lot of sense now, after the next three sections it will make more sense.

BEGIN and END are used to create something called a statement block . A statement block is a group of statements that can be used anywhere one statement can be used. For example, you could write this:

 UPDATE mytable SET emptype = 'manager' WHERE name = 'fred' UPDATE mytable SET name = 'george' WHERE id = 42

Or, if you wanted to, this would work:

 BEGIN          UPDATE mytable SET emptype = 'manager' WHERE name = 'fred'          UPDATE mytable SET name = 'george' WHERE id = 42 END

Note that traditionally the indentation is the preferred style, and is not required, but the person who has to modify your code a few weeks after you write it will hunt you down and torture you if you don't indent properly. BEGIN and END must occur as a pair. That's why they are indented as they are: it makes it easy to spot if one of them is missing, and it makes it easy to tell where the statement block ends. You can nest statement blocks as follows :

 BEGIN       UPDATE mytable SET emptype = 'manager' WHERE name = 'fred'       BEGIN             PRINT 'Whoopee, a nested statement block!'       END       UPDATE mytable SET name = 'george' WHERE id = 42 END

But there's no good reason to do that. There is no limit to the nesting, except that eventually you will run out of room to indent, and although that's not a technical limitation (SQL Server doesn't care) it's going to make it very hard to read the batch.

There's one other thing to mention about the BEGIN...END statement. There's a special keyword called RETURN . The RETURN statement is very flexible ”you'll be using it in a lot of different ways here and later in Chapter 9 when you learn about user -defined functions and stored procedures. For now, you need to know what a RETURN does within a statement block. You can use it like this:

 BEGIN       UPDATE mytable SET emptype = 'manager' WHERE name = 'fred'       RETURN       UPDATE mytable SET name = 'george' WHERE id = 42 END

If you do that, the second UPDATE never runs. Not very useful now, but when you read through the next section on IF...THEN...ELSE , you'll understand.