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
|1||IIF was introduced in SQL Server 2012||CASE was introduced in SQL server 2008|
|2||IIF is a function||CASE is an expression|
|3||Used to return one of the two values.||Used to return one from two or more values.|
IIF ( boolean-expression, value-for-true, value-for-false )
WHEN when-expression THEN result-expression [ …n ]
[ ELSE else-result-expression ]
@i int = 100,
@j int = 200;
SELECT IIF ( @i > @j, ‘GREATER’, ‘SMALLER’ ) AS Result;
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’
- Comparison between SELECT and SET.
- Differences between VARCHAR(MAX) and VARCHAR(N).
- Usage and performance comparison between Union and Union All.