Conditional Logic in Queries

IF...ELSE Statements

If...Else statements allow you to add some conditional logic to your SQL queries or scripts. The lovely MSDN syntax for IF...ELSE statements in SQL Server 2008 R2 looks like this:

1. IF Boolean_expression { sql_statement | statement_block }
2.     [ ELSE { sql_statement | statement_block } ]

Boolean_expression What is meant by the Boolean_expression? A boolean expression is any expression that returns either TRUE or FALSE as its result. When using IF...ELSE statements in SQL Server 2008 R2, if the boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses. If the expression evaluates to TRUE, the sql_statement or statement_block will execute, otherwise if it is FALSE, the sql_statement or statement_block in the ELSE clause will execute (if available).


sql_statement | statement_block What is meant by sql_statement | statement_block? Well, an sql_statement is any valid single SQL statement, and a statement_block is a group of valid SQL statements within a BEGIN...END block.

---

A simple example. Here is a fairly basic example of an IF...ELSE statement in SQL Server:

1. IF 1 = 1 PRINT 'Boolean_expression is true.'
2. ELSE PRINT 'Boolean_expression is false.' ;

What the above query is doing is checking if 1 is equal to 1. And it is. So the statement returns TRUE and then 'Boolean_expression is true.' is printed to the screen. If we changed the boolean expression to '1 = 2', it would return FALSE and 'Boolean_expression is false.' would be printed to the screen.

1. IF 1 = 2 PRINT 'Boolean_expression is true.'
2. ELSE PRINT 'Boolean_expression is false.' ;

Here is an example using the Adventure Works database

1. USE AdventureWorks;
2. GO
3. DECLARE @AvgWeight decimal(8,2), @BikeCount int
4. IF
5. (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
6. BEGIN
7.    SET @BikeCount =
8.         (SELECT COUNT(*)
9.          FROM Production.Product
10.          WHERE Name LIKE 'Touring-3000%');
11.    SET @AvgWeight =
12.         (SELECT AVG(Weight)
13.          FROM Production.Product
14.          WHERE Name LIKE 'Touring-3000%');
15.    PRINT 'There are ' + CAST(@BikeCount AS varchar(3)) + ' Touring-3000 bikes.'
16.    PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.';
17. END
18. ELSE
19. BEGIN
20. SET @AvgWeight =
21.         (SELECT AVG(Weight)
22.          FROM Production.Product
23.          WHERE Name LIKE 'Touring-3000%' );
24.    PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.' ;
25. END ;
26. GO

CASE Statements
There are two types of CASE statements in SQL Server: simple CASE expression and searched CASE expression.

Simple CASE Expression
The simple CASE expression works by comparing the first expression to the expression in each WHEN clause, to see if they are equal. If they are,then the expression in the THEN clause is returned.

Here is an example of the simple CASE expression:

1. USE AdventureWorks;
2. GO
3. SELECT   ProductNumber, Category =
4.       CASE ProductLine
5.          WHEN 'R' THEN 'Road'
6.          WHEN 'M' THEN 'Mountain'
7.          WHEN 'T' THEN 'Touring'
8.          WHEN 'S' THEN 'Other sale items'
9.          ELSE 'Not for sale'
10.       END,
11.    Name
12. FROM Production.Product
13. ORDER BY ProductNumber;
14. GO

What the above query is doing is selecting the category, which is stored as a single letter, such as "R", and then determining which category it is, and returning a category name which is easier to understand.

Searched CASE Expression
The Searched CASE Expression checks the boolean expressions in the order they are typed. It returns whichever one is evaluated to TRUE first. If none of them return TRUE, the searched CASE statement returns whatever is in the ELSE clause, or NULL if there is no ELSE clause specified.

1. USE AdventureWorks;
2. GO
3. SELECT   ProductNumber, Name, 'Price Range' =
4.       CASE
5.          WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
6.          WHEN ListPrice < 50 THEN 'Under $50'
7.          WHEN ListPrice >= 50 AND ListPrice < 250 THEN 'Greater than, or equal to, $50 but Under $250'
8.          WHEN ListPrice >= 250 AND ListPrice < 1000 THEN 'Greater than, or equal to, $250 but Under $1000'
9.          ELSE 'Over $1000'
10.       END
11. FROM Production.Product
12. ORDER BY ProductNumber ;
13. GO

What the above query will do is check the price of a product, based on the ProductNumber, and then assign a text comment based on the price of that product if it falls within a certain numeric range.