Conditional Statements and Branching with IF...THEN...ELSE

So far, all the scripts you've seen start at the top and go to the bottom. There's no way of conditionally executing any statements; all the statements execute, top down. That's not good. So SQL Server provides you with a couple of different ways to do something just in case something else is true.

So, you want to actually write a batch that does something now? Need to insert a piece of data, but don't want to insert a duplicate? How about wanting to make sure that the data you're about to insert follows the rules for that type of data? Well, now you need to understand conditional execution and the IF...ELSE construct.

In T-SQL, an IF statement looks like this:

 IF <expression>       <statement> ELSE       <statement>

The expression has to be an expression that evaluates to a true or false condition, unlike some languages that use zero and non-zero . To evaluate something to true or false, you need to use the comparison operators ”the same comparison operators you used to compare things in SELECT statements from Chapter 4, "Querying and Modifying Data." So, if you want to see whether your UPDATE statement actually changed any data, you could write something like this:

 UPDATE mytable SET emptype = 'manager' WHERE name = 'fred' IF @@ROWCOUNT > 0       PRINT 'There were rows changed'

But what if no rows changed? How can you print a message for that? Use the ELSE part of the logic:

 UPDATE mytable SET emptype = 'manager' WHERE name = 'fred' IF @@ROWCOUNT > 0       PRINT 'There were rows changed' ELSE      PRINT 'There were no rows changed'

Note once again the use of indentation. This is another example of optional, traditional indentation that you should do to make your code more readable.

Now imagine that you want to run the UPDATE statement only if Fred is already not a manager. You could write something like this:

 IF (SELECT emptype FROM mytable WHERE name = 'fred') <> 'manager'       UPDATE mytable SET emptype = 'manager' WHERE name = 'fred' ELSE       PRINT 'There were no rows changed'

It would be helpful if you could put more than one statement in there, to put that PRINT statement back. But unfortunately , the IF statement can take only one statement. The solution is to make several statements look like just one statement.

 IF (SELECT emptype FROM mytable WHERE name = 'fred') <> 'manager' BEGIN       UPDATE mytable SET emptype = 'manager' WHERE name = 'fred'       PRINT "There were rows changed" END ELSE      PRINT 'There were no rows changed'

That's better. Note the use of BEGIN and END . An IF statement, like the WHILE statement that will be covered shortly, can operate on only one statement, so you need to use the BEGIN and END constructs to make it all look like one statement.

A few more examples are probably in order. Imagine that you want to insert a new record only if there aren't any existing records that match certain criteria. You could write something like this:

 if NOT exists (SELECT * FROM mytable WHERE emptype = 'manager') BEGIN       UPDATE mytable SET emptype = 'manager' WHERE name = 'fred'       PRINT 'There were rows changed' END ELSE      PRINT 'There were no rows changed'

The EXISTS() function returns true if even one row in the enclosed select statement returns rows. This type of logic provides a lot of flexibility in script writing by making it easy to check that data meets certain criteria before you perform an insert.

IF constructs can also be nested in a couple of different ways. First, you can create scripts that chain together several constructs:

 IF (SELECT emptype FROM mytable WHERE name = 'fred') <> 'manager' BEGIN       UPDATE mytable SET emptype = 'manager' WHERE name = 'fred'       PRINT 'There were rows changed' END ELSE BEGIN       IF (SELECT emptype FROM mytable WHERE name = 'wilma') <> 'manager'             BEGIN                   UPDATE mytable SET emptype = 'manager' WHERE name = 'wilma'             END END

Notice two significant things in that example. First, the statement has a fallback, if 'fred' is already a manager, it sets 'wilma' to manager also. An ELSE statement could be put into the bottom IF statement to print out the No records changed messages also. Another thing to notice is that the BEGIN-END pairs in the ELSE clause of the construct are optional and, in this case, not required. It could be rewritten like this:

 IF (SELECT emptype FROM mytable WHERE name = 'fred') <> 'manager' BEGIN       UPDATE mytable SET emptype = 'manager' WHERE name = 'fred'       PRINT 'There were rows changed' END ELSE       IF (SELECT emptype FROM mytable WHERE name = 'wilma') <> 'manager'                   UPDATE mytable SET emptype = 'manager' WHERE name = 'wilma'

The argument could be made that the first example is more readable, but both are correct. You can also nest an IF statement this way:

 IF (SELECT emptype FROM mytable WHERE name = 'fred') <> 'manager' BEGIN       UPDATE mytable SET emptype = 'manager' WHERE name = 'fred'       PRINT 'There were rows changed'       IF (SELECT emptype FROM mytable WHERE name = 'wilma') <> 'manager'                   UPDATE mytable SET emptype = 'manager' WHERE name = 'wilma' END

This code behaves differently. It sets 'wilma' to be a manager if 'fred' is not a manager after setting 'fred' to be a manager also.

Next, you need to learn how to loop in T-SQL. That is done using the WHILE construct.