SQL Server 2012 - IIF ( ) and CHOOSE ( )

IIF - Returns one of two values, depending on whether the Boolean expression evaluates to true or false.

Syntax:

IIF ( boolean_expression, true_value, false_value )

Examples :

SELECT IIF(1>2,'TRUE','FALSE')
GO
 -- output: FALSE 
 
 
 
SELECT IIF(2>1,'TRUE','FALSE')
GO 
 -- output: TRUE
 
 
SELECT IIF(1>2,NULL,NULL)
GO 
 -- Msg 8133, Level 16, State 1, Line 1
-- At least one of the result expressions in a CASE specification must be an expression other than the NULL constant. 
 

DECLARE @Exp CHAR(5)
SELECT IIF(1>2,NULL,@Exp)
GO
 -- output: NULL

CHOOSE - Returns the value at the specified index from a list of values. 

 Syntax:

 CHOOSE ( index, val_1, val_2 [, val_n ] )

EXAMPLES:

DECLARE @OrganizationLevel TINYINT = 1
SELECT CHOOSE(@OrganizationLevel,'Chief Executive Officer','Vice President of Engineering',
             'Engineering Manager','Senior Tool Designer') JobTitle 
 
  -- output: Chief Executive Officer 
 
 
 
--if the specified index is out of range 
DECLARE @OrganizationLevel1 TINYINT = 5
SELECT CHOOSE(@OrganizationLevel1,'Chief Executive Officer','Vice President of Engineering',
             'Engineering Manager','Senior Tool Designer') JobTitle  
 
  -- output: NULL 

Reference :

IIF 
CHOOSE 

 See Also:

My other posts on T-SQL