Using a SQL Server Case Statement for IF/Else Logic

By: Tim Wiseman   |   Comments (6)   |   Related: 1 | 2 | 3 | 4 | 5 | > TSQL

Problem

Solution

T-SQL provides the case expression which can be used to provide a switch, similar to an if/else construct in other languages, within a query. There are two slightly different constructs for the case expression: a simple case expression which can only evaluate equality, and a searched case expression which allows for more nuanced comparisons.

Simple Case Expression

Simple case expressions take a single input expression and then compare it for equality to a series of specific values covered by when clauses. The resulting expression for each value is supplied by a then clause. Else may optionally be used to specify a resulting value for when no match was found. This is easier to see with examples. So, to look at some examples, let's start with a table that lists a few Medal of Honor recipients from the Army.

create table dbo.MedalOfHonor (
 Id int identity (1,1) primary key,
 MilRank varchar(50),
 SoldierName varchar(50),
 YearOfAction varchar(4)
 )

insert into dbo.MedalOfHonor (MilRank, SoldierName, YearOfAction)
values
 ('Staff Sergeant', 'Ty Carter', '2009'),
 ('Captain', 'Florent Groberg', '2012'),
 ('Major', 'William D. Swenson', '2009'),
 ('Corporal', 'Frank L. Anders', '1899'),
 ('First Lieutenant', 'George C. Shaw', '1903')

The table lists out the rank, but not the paygrade of the recipients. If we wanted a query to return the paygrade, we could use a case expression to correlate them. It could look like:

select 
 MilRank,
 SoldierName,
 case MilRank
  when 'Staff Sergeant' then 'E-6'
  when 'Captain' then 'O-3'
  when 'Major' then 'O-4'
  when 'Corporal' then 'E-4'
  when 'First Lieutenant' then 'O-2'
  else 'No Rank Given'
 end as 'PayGrade'
from 
 dbo.MedalOfHonor

As an alternative, this use of Case can be performed by creating a table with the appropriate data and joining them rather than using the case expression. Generally, this will be the better way to go when the comparisons will be made in many separate queries, especially when there are many possibilities that would need to be listed in the case expression. Here, this would look like:

insert into dbo.RankToPayGrade (MilRank, PayGrade)
values
 ('Staff Sergeant','E-6'),
 ('Captain', 'O-3'),
 ('Major', 'O-4'),
 ('Corporal', 'E-4'),
 ('First Lieutenant', 'O-2')

select 
 M.MilRank,
 M.SoldierName,
 P.PayGrade
from 
 dbo.MedalOfHonor M
 join dbo.RankToPayGrade P
  on M.MilRank = P.MilRank

This gives the same results as above. But the logic is not quite identical. The case expression provided a convenient else function to catch any ranks that were missing, either because the table was lacking them or because they were not listed a possibility in the case expression's list of when comparisons. We can more fully replicate the logic by using a left join and coalesce. Like:

select 
 M.MilRank,
 M.SoldierName,
 coalesce(P.PayGrade, 'No Rank Given') as Paygrade
from 
 dbo.MedalOfHonor M
 left join dbo.RankToPayGrade P
  on M.MilRank = P.MilRank

Although this query using coalesce produces the same results as the original query with the case expression, they get there by different paths with different execution plans.

Searched Case Expression

Instead of taking a single input expression that is compared for equality, a searched case expression takes a series of expressions to evaluate for truth and returns value in the result expression for the first part that evaluates as true. For example, if we want to differentiate between officers and enlisted, it could look like:

  
select 
 MilRank,
 SoldierName,
 case
  when MilRank in ('Captain', 'Major', 'First Lieutenant') then 'Officer'
  when MilRank in ('Staff Sergeant', 'Corporal') then 'Enlisted'
  else 'Other'
 end as Officer
from 
 dbo.MedalOfHonor M
order by Officer

Since this version of the case expression may accept any Boolean expression instead of evaluating a strict equality, it is important to remember that it will return the result expression for the first when clause that comes up true even when more than one of them could come up true. For instance, if we want to find the century for the action the Medal of Honor was issued for:

select 
 SoldierName,
 YearOfAction,
 case
  when YearOfAction > ='2001' then '21st'
  when YearOfAction >= '1901' then '20th'
  when YearOfAction >= '1801' then '19th'
 end as Century
from dbo.MedalOfHonor
order by Century desc

In that case, it provides the correct results, but only because we put the options in the right order. Captain Groberg was recognized for actions in 2012. That is clearly after 1801 and 1901 as well as 2001. The query provides the correct results only because we put the options in sequence so that it will stop looking when it sees that his year of action was on or after 2001.

Locations of Case Expressions

Case expressions can be put into a SQL statement anywhere it would accept an expression. This includes the where, order by, and having clauses and they can be used for update, delete, and merge statements just as easily as using them with a select statement. For instance, if we wanted to simplify our MilRank information to only show whether the person was an officer or enlisted, we could write an update statement like:

update dbo.MedalOfHonor
set
 MilRank = (CASE
    when MilRank in ('Captain', 'Major', 'First Lieutenant') then 'Officer'
    when MilRank in ('Staff Sergeant', 'Corporal') then 'Enlisted'
    else 'Other'
    end)

select * 
from dbo.MedalOfHonor

Conclusion

The case expression is a flexible and effective way of adding conditional logic into a SQL statement. It can often server a function similar to an If/Else construct in other languages. In many cases, if the same simple case expression will be used repeatedly or with a large number of possible matches, it may be better to use a join with a new table containing the information.

Next Steps

• Armando Prato provides ways to use case expression in place of dynamic SQL.
• Ken Simmons provides more details on the uses of Coalesce.
• Jeremy Kadlec provides more examples of using join.

About the author

Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips