IIF vs CASE

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.