Question: What is Alternative to CASE Statement in SQL Server?
Answer: IIF Function.
Honestly, when I write this blog post I feel a bit sad that IIF Function has not received necessary adoption since its release in the year 2012. When I am writing this blog post it is the year 2018 and it has been 6 years since IIF function released and I see absolutely very little adoption of this feature.
Let us see the following two examples how CASE statement works and how IIF can be its alternative in some situation. Please note that CASE Statement can be very complicated in some scenarios and IIF can’t handle all the scenarios, however, most of the time CASE statement written for either this or that scenarios and IIF can be very much handy functions in those situations.
Let us see this the simple case of statement
SELECTÂ CASE WHENÂ -1Â <Â 1Â THENÂ 'TRUE' ELSEÂ 'FALSE'Â END ASÂ Result;
Now let us convert the CASE statement to IIF function
SELECT IIF ( -1 < 1, 'TRUE', 'FALSE' ) AS Result;
Let us see how both of the queries work and provides the results.
You can see that in both of the above queries if we have to make a decision between two values, it would be easy to write IIF. However, there is something preventing users to use IIF. I would love to know your opinion why do you not use IIF function instead of CASE statements?
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
46 Comments. Leave new
IIF takes more cost to execute than CASE …… observed practically today
Case expressions may only be nested to level 10.
This only works if you have a single “When” statement. In order to use IIF to accomplish the same thing, you will have all different columns, rather than the same column Name with a CASE.
For example, if you have the following:
Select
Case
When A = B Then ‘First Case’
When C = D Then ‘Second Case’
Else ‘Third Case’
End
From TableName
And you modify it to use IIF…
Select
IIF(A=B, ‘First Case’, ‘Third Case’)
, IIF (C=D, ‘Second Case’, ‘Third Case’)
From TableName
The second option will give you two separate columns, rather than a dingle column that the CASE statement provides.
Why wouldn’t you just nest the second IIF, to maintain a single column?
pinal i have one query which have lots of case statements in select section which eventually kills the query performance.
taking that in mind what should be the alternative