IIF vs CASE

IIF vs CASE in SQL Server

by

In this article, we’ll go through IIF and CASE and then compare IIF vs CASE.

IIF is one of the logical Transact-SQL function which returns one among the two values, based on the boolean expression. On the other hand, CASE is a language expression which evaluates a list of conditions and returns one among multiple values, based on the boolean expression.

So, IIF function can be easily replaced with CASE expression. Simultaneously a CASE expression which could return ane among two values can also be interchanged with IIF function.

Now, let us see the major differences of these two options:

IIF vs CASE

#

IIF

CASE

1IIF was introduced in SQL Server 2012CASE was introduced in SQL server 2008
2IIF is a function
CASE is an expression
3Used to return one of the two values.Used to return one from two or more values.
4Syntax:

IIF ( boolean-expression, value-for-true, value-for-false ) 		Syntax:

CASE input-expression
WHEN when-expression THEN result-expression [ …n ]
[ ELSE else-result-expression ]
END
5Example:

DECLARE
@i int = 100,
@j int = 200;
SELECT IIF ( @i > @j, ‘GREATER’, ‘SMALLER’ ) AS Result;		Example:

DECLARE @i int = 200
SELECT CASE @i
WHEN 100 THEN ‘ONE HUNDRED’
WHEN 200 THEN ‘TWO HUNDRED’
WHEN 300 THEN ‘THREE HUNDRED’
ELSE ‘Something Else’
END;

Related Articles

Reference


2 thoughts on “IIF vs CASE in SQL Server”

  1. I like IIF() because it’s faster and cleaner than CASE. However, having said that, I use IIF() only if there is only one condition to evaluate. CASE was built for multiple condition evaluations and does an excellent job with them, whereas multiple condition evaluations with IIF() leads to nested IIF() statements which can get real ugly real fast. Anyone who has created complicated IF() formulas in Excel knows what I’m talking about.

    Reply

  2. Excellent Comparison, I will suggest to use SQL case over IIF as it is much easier to use, but if there are lots of CASE it can make code difficult to read.
    If anyone needs more SQL switch case example, you can check this article

    https: //qawithexperts.com /article /sql /understanding-sql-server-switch-case-with-example /147
    Thanks

    Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.