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 is useful in join and where clauses when returning data though i ideally have more than two alternatives which leads to multi bracket hell with embeddediif () calls
I must use many different database flavors. I try to use SQL as generic as possible, because I never know when I need to use one database flavor or another. Basically, it’s all about the requirements of the job. SQL 92 is more widely supported.
Select case statements are easier to read.
Case is better readability and extendability.
Case is much more flexible.
You can use it with multiple choices, different conditions, and also with value based choices, eg:
CASE @variable WHEN 1 THEN ….. WHEN 2 …. etc
Also IIF is not standard SQL command (ANSI).
Big fan of your blog. I totally agree. I have virtual replaced my use of case when with iif (except where warranted) over the last 2 years and it’s really made for some simpler code when I’ve had to revisit it.
I like the case in all cases bc I can indent the code to show complex logic in the select clause and in the where clause I use or and parentheses
If there are more WHEN clauses( case when i =5 then .. when i= 4 then .. else .. end ) , IIF cant be used right?
I tend to only use iif statements in SSRS expressions. I prefer case statements as it’s far easier for me to use multiple when clauses rather than nesting iif statements and forgetting how many brackets need closing (rather than forgetting to end my case statement). I also think the other reason is simply that once you learn a function that achieves what you need, there is very little incentive to find another solution. Especially if, like me, you have been happily using case statements for 10 years before iif became an option. For those reasons alone I think its an unfair question for an interview. A better one might be to ask what type of join is there other than inner and left, right and full outer. Cross joins are rarely used, but having them in your toolbox can be a life saver.
Two reasons I don’t use it and why I wouldn’t recommend using it. First, it was introduced in SQL Server 2012. Second, CASE is ANSI standard, IIF is not.
I find using IFF results in much tighter (=easier to read) code. If I recall correctly, there is no impact to execution as IIF gets converted to CASE WHEN prior to evaluation. I use it in both SELECT and WHERE clauses.
If IIF gets converted to CASE before evaluation, then there is no case for IIF to be used.
I love IIF since it’s what most programming languages have, but there are two reasons I don’t use IIF:
1. The statement is too complicated, let’s say more than 3 conditions. Then I have to write IIF(IIF(IIF())). Nah, CASE is easier to read.
2. Production server runs with SQL 2008 or below. Trust me, there are a lot of them is still using 2008.
The CASE variant is more expandable, cause you can every time use more conditions.
In my opinion, it is also better to read, because you don’t execute an function with 3 reduced letters but a normal SQL syntax. I know the function also from the old VB age. The new C# generation is not know the strange reduced function.
So you are good do go with CASE WHEN
For a simple true or false option as your example, IIF works fine. For multiple options, CASE seems more eloquent and easier to read, for me anyway.
Where iif really shines is in the use of nested iif functions, such as predicting false values and creating a condition tree categorizing additional types of false values until they are true or defaulted, which is much fewer keystrokes than nested case statements, like-
iif((condition),(value if true), (iif ((another value condition based off previous ‘false’ evaluation), (value if nested condition is true),((value if false or further iif functions)))), or
iif(a-b >100, ‘grtr_thn100’,iif (a-b > 50 and a-b <=100,'between50&100','lessthan50'))
I think people gravitate towards case because of syntax comfort, and the logical idea that you only write evaluations based on true 'when/then' conditions except for an 'else' default value. 'When this is true, do this, when this is true, do this, or else do this' is simpler than 'iif this condition is true do this, if its not heres another function, and iif the condition in that is true, do this, and if false then do this (or heres another iff function).
I believe the iff function is more visually algebraic, and the normal sql developer hated 6th grade as much as everyone else.
I was raised on the IIF but I discovered that nesting is much easier with CASE.
1. CASE statements are easy to read and troubleshoot
2. WHEN/THEN syntax is more natural to humans than IIF(definition, results if true, results if false). That’s the power of SQL language – its very natural as a native human language SELECT, WHEN/THEN, IF, ELSE, WHERE, FROM, ORDER BY, GROUP BY
3.In many situation CASE statement is used with more complex conditions than comparing two numbers (e.g. -1 < 1) for example WHEN X=1 AND Y=2 AND X=3 AND LEN(string)=10 THEN 1 ELSE 10. For such scenario using IIF would not be very readable. And that's very easy example because many time people have to use multiple WHEN/THEN conditions in the CASE statement.
4. CASE is very universal while IIF is only useful for simple comparison on a daily basis
5. Developers can cover most of the scenario with CASE so many times they just use CASE for all of them without bothering of IIF
Personally I use CASE on a daily basis but I like IIF for it's simplicity so when I need to compare something quickly in one simple line I prefer IIF :)
I would never use the IIF function. It looks like a spreadsheet and not like SQL. Also, let’s be honest, I’m a fanatic about the ANSI/ISO standards.
The CASE expression (expression, not statement! I wish people would finally get that right) was derived from the PL/1 programming language when we approve this in ANSI X3H2. There was some discussion about creating an “if – then – else” syntax, with the rule that if you use just the “if – then” it was implied that the UNKNOWN evaluations would fail. Fortunately or unfortunately,, SQL has a three value logic. The UNKNOWN value is associated with either theTRUE result (DDL tests) for the FALSE result (DML tests) in the language. So to get around that problem, someone briefly discussed having a “if – then – else – otherwise” construct. The “otherwise” clause would handle the UNKNOWN values explicitly.
I was really pleased when IIF appeared in SQL2012. It took me back to my days as an Access developer so I use it all the time just for its retro-charm!
1. Is there any performance impact by using this IIF statement?
2. How about more than 1 condition handled in IIF? (Case1,case2,case3,etc,…)
Hope many IIF loops should be handled this situation .