The IF…ELSE and the IF…ELSE IF…ELSE structures are the most basic decision structures you should know if you are just starting out with SQL or any other programming/query language.
If you are familiar with other languages besides SQL, you will already be familiar with the concept of decision structures. But if you aren’t, you are going to get a full breakdown in this tutorial.
Basically, when writing SQL code, sometimes you want certain code to execute if a condition is true, and a completely different set of code to execute if the condition is false.
That is exactly what the IF…ELSE and IF…ELSE IF…ELSE decision structures are designed to do. In this tutorial, we will discuss those structures:
- The IF…ELSE Structure
- The IF…ELSE IF…ELSE Structure
- Tips, tricks and links
If you’re ready, let’s start from the top!
1. The IF…ELSE Structure
The IF…ELSE structure will execute a certain block of code if a specified condition is TRUE, and a different block of code if that condition is FALSE.
Here is the basic layout and syntax of the IF…ELSE structure:
IF(<condition is true>) BEGIN <execute some code> END ELSE BEGIN <execute some different code> END
There are a couple of things to point out about this syntax:
- The use of BEGIN…..END in the bodies of both our IF block and our ELSE block is necessary if the body is more than one line long. If your body is only one line long, you can leave those keywords out.
- In the IF block, we wrapped our condition around parentheses. This is not required, but I think it makes the code a bit more readable.
Not too complicated, right? You’re simply checking some condition and acting accordingly. But when are you likely to use an IF…ELSE block?
You will most likely see and write decision structures within stored procedures.
If you aren’t sure what a stored procedure is, you should definitely check out my full tutorial here:
Stored Procedures: The Ultimate Guide for Beginners
Stored procedures basically allow us to outsource some SQL work into a database object we can run on demand. So instead of needing to write the same complex code from scratch all the time, we just write it once in a stored procedure and call the stored procedure every time we want that work performed.
Again, you should check out the full tutorial first.
An example of using IF…ELSE in a stored procedure
So let’s think about a useful stored procedures. Let’s say you are the owner of a chain of retail stores in the state of Colorado. You have two stores located in Denver and Boulder.
Each store has their own respective table in your database to collect information about customers who shop at each store. The tables are called DenverStore and BoulderStore.
Here is an example of the content of the DenverStore table:
Great, so maybe we can write a simple stored procedure to insert a new customer into our database. We want to make sure this stored procedure can insert into either the DenverStore table or the BoulderStore table.
Ideally, the stored procedure would be called like this:
Let’s look at what we’re passing to the stored procedures. The first parameter is the store location the customer shops at, which corresponds to the table we want to insert the new customer into. The rest of the parameters are the customer’s first name, last name and email.
So if the city is ‘Denver’, we want the stored procedure to insert the customer into the DenverStore table.
Otherwise, if the city is ‘Boulder’, we want the stored procedure to insert the new customer into the BoulderStore location.
Let’s take a look at the definition of our stored procedure:
So this stored procedure uses the IF…ELSE decision structure to do exactly what we want. If the city passed in is ‘Denver’, we insert into the DenverStore table. Otherwise, we must want to insert the new customer into the BoulderStore table.
2. The IF…ELSE IF…ELSE Structure
The IF…ELSE IF…ELSE decision structure allows you to specify more than one condition, unlike the IF…ELSE structure. The first condition resulting in TRUE gets executed. If none of the conditions are true, the block of code in the ELSE gets executed.
Here is the basic layout of the IF…ELSE IF…ELSE decision structure:
IF(<condition is true>) BEGIN <execute some code> END ELSE IF(<different condition is true>) BEGIN <execute some other code> END ELSE BEGIN <execute some other other code> END
So SQL Server will check the conditions one at a time, starting from the top. The first condition that evaluates to true will execute the corresponding block of code. All other conditions get skipped and their code does not run.
If none of the IF…ELSE IF conditions are true, the code in the ELSE block is ran instead.
And again, the following two things are true about IF…ELSE IF…ELSE:
- You don’t need to use the BEGIN…END keywords if the body of your IF, ELSE IF, or ELSE block is only one line long
- The parentheses around your conditions are optional.
An example of using the IF…ELSE IF…ELSE decision structure.
We made an assumption in the IF…ELSE structure that we shouldn’t be making. We assumed if the user passes anything but the ‘Denver’ city into the stored procedure, they must mean they want the new row inserted into the BoulderStore table.
What if the user passes the word ‘Spagetti‘ into the stored procedure?:
What happens?
Answer: The row gets inserted into the BoulderStore table:
Ok, so we should stop making that assumption. We should change our stored procedure to be a bit more explicit.
If the user explicitly passes ‘Denver’, we insert into DenverStore. If they explicitly pass ‘Boulder’, we insert into BoulderBranch.
Otherwise, if they pass something other than ‘Denver’ or ‘Boulder’, maybe we’ll just return an error message.
This is a great scenario for the IF…ELSE IF…ELSE decision structure. Let’s see it in action:
Notice in my ELSE block, I didn’t need to use the BEGIN….END keywords because the body of my ELSE is only one line.
Now let’s try another bogus call:
Much better!
The IF…ELSE IF…ELSE decision structure is great if there is more than one valid condition you want to check for, but also include a fail-safe if none of the conditions are true.
3. Tips, tricks and links.
Here is a list of tips and tricks you should know when working with decision structures.
Tip # 1: If you need to check for more than one condition in your IF and ELSE IF structures, you can do that by using the AND operator or the OR operator.
The AND or OR operators will check each individual condition to see if the entire condition evaluates to true or false.
When using the AND operator, the individual conditions need to all evaluate to true in order for the entire condition to evaluate to true.
When using the OR operator, only a single condition needs to evaluate to true in order for the entire condition to evaluate to true.
The AND and OR operators behave the same in our decision structures as they do in a WHERE clause, for example. If you are already familiar with how these operators work in a WHERE clause, you should already know how they will work with our decision structures!
Here are some silly examples. Let’s look at the AND operator first:
Since all our conditions are true, the entire thing evaluates to true and we print the message in our IF block.
But if I make just one condition false, the entire thing evaluates to false and we print the message in our ELSE block:
But if I change all the AND operators to OR operators, suddenly were back in the IF block:
Since at least one of these conditions is true, the entire thing evaluates to true and we enter the IF block.
(Notice we changed the messages we’re printing to make a bit more sense with the OR operator)
The only way an OR operator evaluates to false is if all conditions evaluate to false:
The last thing to point out is you can certainly use both AND and OR operators together if you need to. The AND operator will take precedence. Here is an example:
The AND condition is evaluated first, which checks if @value1 is ‘Y’ and @value2 is 200. This AND operator evaluates to false because at least one of those individual conditions is false.
That means the left side of our OR operator is false. But then we need to check the right side of our OR operator. The right side evaluates to true. Since at least one side of our OR operator evaluates to true, the entire thing evaluates to true!
Tip # 2: You can definitely have more than one ELSE IF condition in your IF…ELSE IF..ELSE structure.
Say further down the road we open another store in Colorado Springs. We’ll want to update our stored procedure to make sure we can insert into that table, too:
Links
There is an extremely helpful book that introduces you to many T-SQL topics including decision structures that you should get your hands on. It’s called “T-SQL Fundamentals” by Itzik Ben-Gan. This is one of only a few books I own that helped me understand many SQL Server topics. You definitely won’t regret owning this book, trust me. Get it today!
Next Steps:
Leave a comment if you found this tutorial helpful!
As mentioned earlier, you will most likely see and use these decision structures in your stored procedures. If you need a thorough discussion on what a stored procedure is and how they can help us, check out this tutorial:
Stored Procedures: The Ultimate Guide for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
