Using a SQL Server Case Statement for IF/Else Logic


By:   |   Updated: 2016-12-02   |   Comments (6)   |   Related: More > T-SQL

Problem
How can I incorporate logic similar to an If/Else construct into a T-SQL Query?
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
PayGrade Results

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.

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
Officer Or Enlisted Query

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
Century Query

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
Updated data set

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.


Last Updated: 2016-12-02


get scripts

next tip button



About the author
MSSQLTips author Tim Wiseman Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, January 02, 2017 - 6:06:12 AM - Jonathan Back To Top

If course now IIF is also part of the T-SQL language since SQL Server 2012


Tuesday, December 06, 2016 - 1:35:07 PM - Timothy A. Wiseman Back To Top

Thank you, John and Kris.

T. Traweek. You are right. I apologize for that and thank you for catching it.

Joe Celko, thank you for the clarification. I did not check closely enough and allowed myself to slip into a common error.


Friday, December 02, 2016 - 8:53:47 PM - John Spencer Back To Top

 

 Thanks for the article.  I have used the CASE statement using CASE WHEN xxx in (1,2,3,...) THEN 'value' ELSE 'value1' END columnname.

I am surprised by the flexibility of the CASE statement.  using the 'WhEN xxx IN (1,2,3,...)' enabled me to avoid more convoluted code.


Friday, December 02, 2016 - 11:33:18 AM - T Traweek Back To Top

 Just a minor error, the "> 2001 then 21st" should be ">= 2001 then 21st".

Just goes to show ya, it's always something, ain't it?

 


Friday, December 02, 2016 - 9:51:25 AM - Kris Maly Back To Top

 

Awesome!

 

I enjoyed reading this article/post.

Please keep posting.

 

Thanks for educating the community and appreciate your volunteership


Friday, December 02, 2016 - 8:36:16 AM - Joe Celko Back To Top

 CASE is an expression and not a statement. This is why behaves the way it does.. It is also worth mentioning that the first step in processing the case expression is to determine the highest data type in the then and else clauses. This becomes the data type of the expression (expressions have a data type, statements do not). As a historical note, we got the syntax for this from the ADA programming language.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools