IIF vs CASE in SQL Server

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

Related Articles & Tools

Leave your thoughts...

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