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.

More on Subqueries

The following examples of subqueries, using subqueries inside SELECT statements and in the FROM clause, use the Ice Cream Shop database located here.

Subqueries inside SELECT statements:

1. /*  Using subquery in SELECT statement.
2.     This example uses the IceCreamStore database.
3. */
5. SELECT (SELECT SUM(F.flavorID)) AS 'Varieties Available', B.Name AS 'Brand Name'
6. FROM Flavors AS F
7. INNER JOIN xBrandFlavorDistributors AS XFD
8. ON Xfd.flavorID = F.flavorID
9. INNER JOIN Brands AS B
10. ON XFD.brandID = B.brandID
11. GROUP BY B.Name;

Subqueries inside the FROM clause:

1. /*  Using subquery in FROM clause.
2.     This example uses the IceCreamStore database.
3. */
5. SELECT F.Name
6. FROM (
7.         SELECT * FROM Flavors
8.      ) AS F

Writing to the Console Window

If you open Visual Studio, then create a new console application, you will discover it is really quite easy to write lines of text to the console window. If you are unsure what a console window looks like, it is very similar to Command Prompt, or you could just look at the image included with this post.
To write lines of text to the console window, you must access the ‘Console’ class object. You do this by typing ‘Console’ and then a period ‘.’ to access its methods and properties. You use the ‘WriteLine()’ method which can accept a string as an argument, such as “Hello user.”

• VB
• C#

   1:  Module SimpleConsole

   2:   

   3:      Sub Main()

   4:   

   5:          ' Write a line to the console window.

   6:          Console.WriteLine("Hello user.")

   7:   

   8:          ' Wait for the user to press enter before exiting.

   9:          Console.ReadLine()

  10:   

  11:      End Sub

  12:   

  13:  End Module

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Linq;

   4:  using System.Text;

   5:   

   6:  namespace SimpleConsole

   7:  {

   8:      class Program

   9:      {

  10:          static void Main(string[] args)

  11:          {

  12:   

  13:              // Write a line to the console window.

  14:              Console.WriteLine("Hello user.");

  15:   

  16:              // Wait for the user to hit enter before exiting.

  17:              Console.ReadLine();

  18:          }

  19:      }

  20:  }

The above example will produce the following result:

The Ice Cream Shop Revisited

In my 'The Ice Cream Shop' post I provided a query to create a simple database system for storing ice cream flavors and ice cream brands. I then provided queries to perform the following searches on our database:

• Select all brands that sell a particular ice cream flavor
• Select all flavors offered by a particular brand

Now we want to expand our Ice Cream Shop database to allow for the following actions/features:

• Keep track of ice cream inventory. We want to know how much ice cream we have for each flavor and brand. We also want a run a report to tell us which ice cream's are low.
• Keep track of ice cream distributors. We want to be able to find distributors that sell specific flavors and brands of ice cream.
• Keep track of ice cream sales. We want to be able to keep track of which flavors and brands of ice cream were sold. We need to know the sale date, how many scoops were sold and what they sold for.

Like last time, I did the work of creating the tables and inserting the records (there was a lot of records in this one).

Download the IceCreamStore Revisited Database:

---

IceCreamStore Revisited for SQL Server 2008
IceCreamStore Revisited for SQL Server 2008 R2

---

Retrieving ice cream inventory that is low:
To do this, we will employ a few techniques: table aliasing, multiple inner joins and conditional where statements.

1. USE IceCreamStore;
2. GO
4. -- Retrieve the low ice cream inventory. Inventory is low when it is less than or equal to 1 tub.
5. SELECT F.Name AS 'Flavor', B.Name AS 'Brand', I.stock AS 'Inventory'
6. FROM Inventory AS I
7. INNER JOIN xBrandFlavorInventory AS XI
8. ON XI.inventoryID = I.inventoryID
9. INNER JOIN Flavors AS F
10. ON XI.flavorID = F.flavorID
11. INNER JOIN Brands AS B
12. ON XI.brandID = B.brandID
13. WHERE I.stock <= 1;

Our inner joins in the query above link the tables in this order:

• Inventory
• xBrandFlavorInventory
• Flavors
• Brands

What it is doing is linking the Inventory table to the xBrandFlavorInventory table which acts as the 'middle-man' to our Flavors and Brands table.

Retrieving distributors that sell an ice cream flavor/brand combination:

This query employs the following techniques: table aliasing, multiple inner joins and conditional where statements:

1. USE IceCreamStore;
2. GO
4. -- Search distributors for a flavor and brand combination.
5. SELECT D.Name AS 'Distributor'
6. FROM Distributors AS D
7. INNER JOIN xBrandFlavorDistributors AS XD
8. ON XD.distributorID = D.distributorID
9. INNER JOIN Flavors AS F
10. ON xd.flavorID = F.flavorID
11. INNER JOIN Brands AS B
12. ON xd.brandID = B.brandID
13. WHERE F.Name = 'Caramel Ice Cream Bars' AND B.Name = 'Tip-Top';

On line 13, in our WHERE statement, 'Caramel Ice Cream Bars' is our ice cream flavor and 'Tip-Top' is our brand name.

Gathering sales for a specific month:
This query employs the following techniques: table aliasing, multiple inner joins, conditional where statements and SQL Server Functions.

1. -- Get all the sales for a specific month.
2. SELECT F.Name AS 'Flavor', B.Name AS 'Brand', S.saleDate AS 'Sale Date', S.scoops AS 'Scroops', S.price AS 'Price'
3. FROM Sales AS S
4. INNER JOIN xBrandFlavorSales AS XS
5. ON XS.saleID = S.saleID
6. INNER JOIN Flavors AS F
7. ON XS.flavorID = F.flavorID
8. INNER JOIN Brands AS B
9. ON XS.brandID = B.brandID
10. WHERE DATEPART(MONTH, S.saleDate) = '03';

In the query above, specifically on line 10, our WHERE statement is using an SQL Server Function called DATEPART(). This function pulls a part of a date form a date or datetime data type. We can then use the value returned by the DATEPART() function to do some conditional checking. In our case we are seeing if the MONTH is equal to '03', or the numeric equivalent to March.

The Ice Cream Shop

Today we are going to create a database that can be used to store ice cream brands and ice cream flavors. The database should be extensible enough to allow users to search for ice cream by flavor or by the brand. When they search for flavors, it should show all the brands that offer that flavor. And if they search by brand, the system should return all flavors the brand offers.

To simplify things, I have already created the tables and the data inside of them. The queries to accomplish our tasks will be at the bottom of this post.

Download the IceCreamStore Database:

---

These files are scripts you can run to create the database and insert the records.

IceCreamStore for SQL Server 2002

IceCreamStore for SQL Server 2005

IceCreamStore for SQL Server 2008

IceCreamStore for SQL Server 2008 R2

---

Selecting all the flavors for a specific brand:

To select all the flavors from a specific brand, we will use two techniques: inner joins and a cross reference table. A cross reference table is a table that exists 'between' two tables that creates a link for those tables. It usually holds the primary keys of the original two tables to create a relation between those tables.

To select all flavors for a specific brand:

1. USE IceCreamStore;
2. GO
4. -- Select all flavors from a specific brand.
5. SELECT F.Name AS 'Flavor Name'
6. FROM dbo.Flavors AS F
7. INNER JOIN dbo.xBrandsFlavors AS X
8. ON F.flavorID = X.flavorID
9. INNER JOIN dbo.Brands AS B
10. ON X.brandID = B.brandID
11. WHERE B.Name = 'Amul';

In the above query, "Amul" is the name of the brand we want to search for. The query returns all the ice cream flavors that a brand offers.

Selecting all brands that sell a particular flavor:
This query uses the same techniques as selecting the flavors for a specific brand, except it does it in reverse.

To select all brands for a particular flavor:

1. USE IceCreamStore;
2. GO
4. -- Select all brands that sell a specific flavor.
5. SELECT B.Name AS 'Brand Name'
6. FROM dbo.Brands AS B
7. INNER JOIN dbo.xBrandsFlavors AS X
8. ON B.brandID = X.brandID
9. INNER JOIN dbo.Flavors AS F
10. ON X.flavorID = F.flavorID
11. WHERE F.Name = 'Vanilla Ice Cream';

In the above query "Vanilla Ice Cream" is the ice cream flavor we want to search for. The query returns all the brands that offer that ice cream flavor.

Subqueries and SQL Server

Subqueries are an interesting idea. They are, at the most basic level, a select query within a a SELECT, INSERT, UPDATE or DELETE statement, or inside another subquery. Subqueries can be used anywhere an expression allowed.

In an earlier post, located here on self joins, I explained how to create a very basic system to allow the entry of posts and comments on those posts. Near the end of that post, I showed how to use a subquery that allows you to select all posts and the number of comments on that post:

1. SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted On', (SELECT COUNT(T.postID) FROM Posts AS T WHERE T.parentPost = p.postID) AS 'Comments'
2. FROM Posts AS P
3. WHERE P.parentPost IS NULL;

What that query will do is execute the subquery, or inner query, and return the results, which can be returned by themselves, or be used in expressions as constraints.

The remainder of this post will refer to the information presented in the self join post located here.

The following query will return the posts that have comments associated with them. What it will do is execute the subquery and make sure that the value returned is greater than 0. It will use this as the constraint in our WHERE statement.

1. SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted On'
2. FROM Posts AS P
3. WHERE (SELECT COUNT(T.postID) FROM Posts AS T WHERE T.parentPost = p.postID) > 0;

For even more information on subqueries, you can look at the Subquery Fundamentals provided by MSDN.

Self Join

I've been on a kick about posting on joining tables lately. Sometimes you will come across a situation where you need to join a table to itself when executing a query. You can do this simply by aliasing your tables and using an inner join. There is no 'standard' scenario to explain this concept, so I decided to use a basic forum messaging system as our scenario.

Problem: Design a database where people can make posts, and people can comment on other people's post. Keep in mind that people can also comment on other peoples comments. Only use one table.

Let's start by deciding how our table will be. We want to keep track of the user who posted the comment, the subject and body of the comment, the date the comment was posted, and if the post is a comment, the comment that the post applies to.

Posts

-----------

postID        PK

postedBy    VARCHAR(50)

subject        VARCHAR(50)

body           VARCHAR(500) -- Note: this is an unusually large size for a comment's size.

datePosted  DATE

parentPost   INT

So, at this point we can create our database and table:

1. USE master;
2. GO
4. CREATE DATABASE Forum;
5. GO
7. USE Forum;
9. -- Create a Posts table.
10. CREATE TABLE Posts
11. (
12.     postID INT IDENTITY(1,1) PRIMARY KEY
13.     , postedBy VARCHAR(50)
14.     , [subject] VARCHAR(50)
15.     , body VARCHAR(500)
16.     , datePosted DATE
17.     , parentPost INT -- This field will be used for comments on our posts.
18. );

Next, we will insert some records to play with:

1. -- Insert some test records into our Posts table.
2. INSERT INTO Posts
3. (
4.     postedBy
5.     , [subject]
6.     , body
7.     , datePosted
8. )
9. VALUES
10. (
11.     'Self Destruct'
12.     , 'Test Message'
13.     , 'This is test message.'
14.     , '3/17/2011'
15. );
17. INSERT INTO Posts
18. (
19.     postedBy
20.     , [subject]
21.     , body
22.     , datePosted
23. )
24. VALUES
25. (
26.     'Self Destruct'
27.     , 'Test Message #2'
28.     , 'This is another test message.'
29.     , '3/18/2011'
30. );
32. INSERT INTO Posts
33. (
34.     postedBy
35.     , [subject]
36.     , body
37.     , datePosted
38. )
39. VALUES
40. (
41.     'Self Destruct'
42.     , 'Test Message #3'
43.     , 'This is yet another test message.'
44.     , '3/18/2011'
45. );
47. INSERT INTO Posts
48. (
49.     postedBy
50.     , [subject]
51.     , body
52.     , datePosted
53.     , parentPost
54. )
55. VALUES
56. (
57.     'Self Destruct'
58.     , 'Good post!'
59.     , 'That was a good post'
60.     , '3/18/2011'
61.     , 1
62. );
64. INSERT INTO Posts
65. (
66.     postedBy
67.     , [subject]
68.     , body
69.     , datePosted
70.     , parentPost
71. )
72. VALUES
73. (
74.     'Self Destruct'
75.     , 'Great post!'
76.     , 'That was a great post'
77.     , '3/18/2011'
78.     , 1
79. );
81. INSERT INTO Posts
82. (
83.     postedBy
84.     , [subject]
85.     , body
86.     , datePosted
87.     , parentPost
88. )
89. VALUES
90. (
91.     'Self Destruct'
92.     , 'Lalalala'
93.     , 'allalalalala'
94.     , '3/18/2011'
95.     , 2
96. );
98. INSERT INTO Posts
99. (
100.     postedBy
101.     , [subject]
102.     , body
103.     , datePosted
104.     , parentPost
105. )
106. VALUES
107. (
108.     'Self Destruct'
109.     , 'Bad Post'
110.     , 'That was a bad post.'
111.     , '3/18/2011'
112.     , 3
113. );

Now we got to think about how to get our original posts. Realize that if it is a normal post, it will not have a parentPost because it is not a comment on someone else's post. So we will start there:

1. -- Get all parent posts.
2. SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted On'
3. FROM Posts AS P
4. WHERE P.parentPost IS NULL;

Results of previous query.

Now, let's get all the posts that have comments associated with them. We will do this by creating an inner join from our Posts table, back to our Posts table. What you will need to is create two aliases for your table to compare back to itself. Let's learn by example:

1. -- Let's retrieve all posts that have comments.
2. SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted By', C.[subject] AS 'Comment'
3. FROM Posts AS P
4. INNER JOIN Posts AS C
5. ON P.postID = C.parentPost;

In the above query, we are essentially referencing two copies of the Posts table, I called them P and C in our query. The query uses a simple inner join to grab all records from our C Posts table that have a parentPost value other than null.

The query results look like:

Results of our comments query.

You can use this technique to perform quite a range of cool things, such as comparing a table to itself to narrow query results

Referring back to the posts example, we can use another technique (called sub querying, which will be explained in future post) to obtain posts and the number of comments on that post:

1. SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted On', (SELECT COUNT(T.postID) FROM Posts AS T WHERE T.parentPost = p.postID) AS 'Comments'
2. FROM Posts AS P
3. WHERE P.parentPost IS NULL;

The results look like this:

Results of our sub-query retrieving posts and the number of comments.

Full script:

1. USE master;
2. GO
4. CREATE DATABASE Forum;
5. GO
7. USE Forum;
9. -- Create a Posts table.
10. CREATE TABLE Posts
11. (
12.     postID INT IDENTITY(1,1) PRIMARY KEY
13.     , postedBy VARCHAR(50)
14.     , [subject] VARCHAR(50)
15.     , body VARCHAR(500)
16.     , datePosted DATE
17.     , parentPost INT -- This field will be used for comments on our posts.
18. );
20. -- Insert some test records into our Posts table.
21. INSERT INTO Posts
22. (
23.     postedBy
24.     , [subject]
25.     , body
26.     , datePosted
27. )
28. VALUES
29. (
30.     'Self Destruct'
31.     , 'Test Message'
32.     , 'This is test message.'
33.     , '3/17/2011'
34. );
36. INSERT INTO Posts
37. (
38.     postedBy
39.     , [subject]
40.     , body
41.     , datePosted
42. )
43. VALUES
44. (
45.     'Self Destruct'
46.     , 'Test Message #2'
47.     , 'This is another test message.'
48.     , '3/18/2011'
49. );
51. INSERT INTO Posts
52. (
53.     postedBy
54.     , [subject]
55.     , body
56.     , datePosted
57. )
58. VALUES
59. (
60.     'Self Destruct'
61.     , 'Test Message #3'
62.     , 'This is yet another test message.'
63.     , '3/18/2011'
64. );
66. INSERT INTO Posts
67. (
68.     postedBy
69.     , [subject]
70.     , body
71.     , datePosted
72.     , parentPost
73. )
74. VALUES
75. (
76.     'Self Destruct'
77.     , 'Good post!'
78.     , 'That was a good post'
79.     , '3/18/2011'
80.     , 1
81. );
83. INSERT INTO Posts
84. (
85.     postedBy
86.     , [subject]
87.     , body
88.     , datePosted
89.     , parentPost
90. )
91. VALUES
92. (
93.     'Self Destruct'
94.     , 'Great post!'
95.     , 'That was a great post'
96.     , '3/18/2011'
97.     , 1
98. );
100. INSERT INTO Posts
101. (
102.     postedBy
103.     , [subject]
104.     , body
105.     , datePosted
106.     , parentPost
107. )
108. VALUES
109. (
110.     'Self Destruct'
111.     , 'Lalalala'
112.     , 'allalalalala'
113.     , '3/18/2011'
114.     , 2
115. );
117. INSERT INTO Posts
118. (
119.     postedBy
120.     , [subject]
121.     , body
122.     , datePosted
123.     , parentPost
124. )
125. VALUES
126. (
127.     'Self Destruct'
128.     , 'Bad Post'
129.     , 'That was a bad post.'
130.     , '3/18/2011'
131.     , 3
132. );
134. -- Let's retrieve all parent posts and the number of comments that post has.
135. -- Note: This query contains a subquery.
136. SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted On', (SELECT COUNT(T.postID) FROM Posts AS T WHERE T.parentPost = p.postID) AS 'Comments'
137. FROM Posts AS P
138. WHERE P.parentPost IS NULL;
140. -- Let's retrieve all posts that have comments.
141. SELECT p.[subject] AS 'Subject', p.[body] AS 'Body', p.postedBy AS 'Posted By' , p.datePosted AS 'Posted By', C.[subject] AS 'Comment'
142. FROM Posts AS P
143. INNER JOIN Posts AS C
144. ON P.postID = C.parentPost;