Using a SQL Server Case Statement for IF/Else Logic

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

Development Best Practices for SQL Server

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. Andy Warren will share his many years of experience to give some pointers on what has worked best for him and how you can utilize some of this knowledge.

How can I incorporate logic similar to an If/Else construct into a T-SQL Query?

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)
 ('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:

 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'
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)
 ('Staff Sergeant','E-6'),
 ('Captain', 'O-3'),
 ('Major', 'O-4'),
 ('Corporal', 'E-4'),
 ('First Lieutenant', 'O-2')

 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:

 coalesce(P.PayGrade, 'No Rank Given') as Paygrade
 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:

  when MilRank in ('Captain', 'Major', 'First Lieutenant') then 'Officer'
  when MilRank in ('Staff Sergeant', 'Corporal') then 'Enlisted'
  else 'Other'
 end as Officer
 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:

  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
 MilRank = (CASE
    when MilRank in ('Captain', 'Major', 'First Lieutenant') then 'Officer'
    when MilRank in ('Staff Sergeant', 'Corporal') then 'Enlisted'
    else 'Other'

select * 
from dbo.MedalOfHonor
Updated data set


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

Comments For This Article

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

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 (44908)

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 (44893)


 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 (44890)

 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 (44886)




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 (44885)

 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.


Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

Find MAX value from multiple columns in a SQL Server table

SQL Server Loop through Table Rows without Cursor

get free sql tips
agree to terms