What is Alternative to CASE Statement in SQL Server? – IIF Function – Interview Question of the Week #164

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)