IIF() in SQL 2012

by Valmik 26. June 2012 08:01
We have used IF..ELSE and CASE..END in earlier versions of SQL to perform logical conditional operations.
However, IIF() can be used as a writing conditional CASE statements in a single T-SQL. It evaluates the expression passed in the first parameter with the second parameter depending upon the evaluation of the condition and returns either TRUE or FALSE.
 
In most of the cases we use ISNULL() function, we ofthen need to handle other cases than just comparing the operand with NULL. IIF() serves the purpose of having checks based on as many expressions as you need. However, you can nest the IIF() upto 10 levels only, just like the CASE. 
 
Example:
 
DECLARE @A INT = 10
DECLARE @B INT = 8
SELECT IIF(@A > @B, 'A IS GREATER THAN B', 'B IS GREATER THAN A')
GO;
 
Result of execution of above statement: 
-------------------
A IS GREATER THAN B  
(1 row(s) affected)
 
Another Example with expressions:
 
DECLARE @MIKE_AGE INT = 30
DECLARE @CLARA_AGE INT = 24
SELECT IIF(@MIKE_AGE > @CLARA_AGE, 'MIKE IS OLDER THAN CLARA BY '+LTRIM(STR(@MIKE_AGE-@CLARA_AGE))+' YEARS', 'CLARA IS OLDER THAN MIKE BY '+LTRIM(STR(@MIKE_AGE-@CLARA_AGE))+' YEARS')
GO;
 
Result of execution of above statement:
--------------------------------------------
MIKE IS OLDER THAN CLARA BY 6 YEARS
(1 row(s) affected)

Tags:

Category

Recent Posts

Tag cloud