If Then Else

SQL has its own If Then Else. So let's have a look at that one first. It looks like this:

declare @Date date = '2021-01-01'
declare @CompareDate date = getdate()

if @CompareDate < @Date
--begin
  select 'This is history'
--end
  else if @CompareDate = @Date
--begin
  select 'This is today'
--end
else
--begin
  select 'This is the future'
--end

Let's zoom in on some of the things in this one.

Multiple comparisons

First I do a comparison: is the CompareDate before the Date. If that is the case, it is history.

Now do to another comparison, you can use the else if Statement. And if you need more comparisons, you can use the else if again and again.

When none of your comparisons result to true, you can end it with an else statement.

If you want to do just one comparison, simply don't add an else if or else.

Begin End

In this example I commented out the begin and end statements since they are not really needed here. The reason for that inside of them is only one statement. If you want to do multiple operations, SQL would not now what should be executed conditionaly and what would simply be a next line. Try this one for with and without the begin and end commented out to see the difference:

if 1 = 0
begin
select getdate()
select newid()
end

select 3

if 1 = 0
--begin
select getdate()
select newid()
--end

select 3

The If statement can be used in Triggers, Functions and Stored Procedures. Inside the begin and end you can put anything you want. Updates, Inserts or Deletes, setting values for variables or print out a message.

But you can not use the If statement inside a Select Query, but we will get back to that one later.

Did I stutter??

Microsoft SQL Server has a built-in IIF function which is kindalike a shorthand version of the If statement. It was introduced in SQL 2012 to accomodate the migration of Access databases (which also had this function) to SQL Server. So do note that this is not a true SQL function that you can use on other Non MS SQL databases.

The IIF function has 3 parameters. First you put in the condition. Than the value that should be returned if the condition is true. And finaly the value that should be returned if the condition is not true. Something like this:

declare @CompareDate date = '2021-01-01'
declare @Date date = getdate()
select IIF(@CompareDate = @Date, 'Equal', 'Not Equal')

Whereas with the regular If statement you can also do all kinds of other stuff, the IIF can only be used to specify a value. But as you can see, this one can be used in a Select Statement. So it definetely has its own use cases.

Do note that the output values for true and false need to be of the same Data Type.

If you want to do more comparisons, you can use the IIF function nested inside another one like this:

declare @CompareDate date = '2021-01-01'
declare @Date date = getdate()
select IIF(@CompareDate < @Date, 'This is history', IIF(@CompareDate = @Date, 'This is today', 'This is the future'))

Your code already becomes quite messy with 2 comparisons. And with 3 comparisons your code becomes really ugly and hardly readable.

Luckily there is beter way for that inside a Select Statement.

This is the Case

If you want to do more comparisons inside a Select Statement, you can use the Case. Its syntax is pretty simple and straightforward. To signal you are going to do comparisons, open with Case, and when you are done close with End. Inside of that: When something is true, Then return this. Else return something else.

Here is an example:

declare @CompareDate date = '2021-01-01'
declare @Date date = getdate()
select
case
  when @CompareDate < @Date then 'This is history'
  when @CompareDate = @Date then 'This is today'
  else 'This is the future'
end

As with the IIF function, it can only be used for values. You simply cannot do an Update Statement or something like that inside a Select Statement.

And again, the values that should be set for each comparison should all be of the same Data Type.

As with the If Statement, you can just do one comparison and then End the Case. No need for another comparison or an Else. If the comparion is not true, it will simply set the value to Null.

And you can do some nesting here. Contrary to the IIF function, you can keep this one readable. Just use line-breaks and indentation:

declare @Score int = 7
  select
  case
  when @Score = 10 then 'Perfect'
  when @Score > 6 then
    case
      when @Score > 8 then 'Impressive'
      else 'Good'
    end
  else 'You flunked'
end

One Strike and you're Out!

One thing to keep in mind with the If, IIF and Case is that once a condition is true, SQL Server will ignore every other comparison.

Take a look at this one:

if 1 = 1
  select 1
else if 2 = 2
  select 2
else
  select 3

1 = 1 is obviously true. But 2 = 2 is also absolutely true. But since 1 = 1 is already true, everything that comes after that is ignored.

So for performance reasons, always put the most common case as the first comparison. And then work your way down from there.

This is especially true when you are doing costly Sub-Queries in your comparisons. If your most common case is the last one, all the costly Sub-Queries before that need to be executed. If you put it on top, only that one needs to be performed and it's done.

That's it for Today!

Three ways to do conditional testing in your SQL Statements.

The If statement that can be used in Triggers, Functions of Stored Proceduren, but not in Select Statements. This one is highly flexible because you cannot only use it to return a value. But you can also use it to call a Stored Procedure, run an Update or Insert Statement or whatever you need to do.

The IIF function which is MS SQL Server only. It is easy to write but not realy flexible. And it becomes quite ugly when you are nesting one IIF inside another IIF. But hey, you can use it in Select Statements.

When your conditional testing is more advanced, you can (and should) use the Case construction in your Select Statement. It's very flexible and even in more advanced situations, it's still pretty easy to read.

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!