IIF() is the new built-in logical function introduced as a Part of
Sql Server 2012 .we can replace CASE and IF...ELSE statement
using IIF().
Syntax: IIF ( boolean_expression, true,
false )
If Boolean expression evaluates to TRUE then Second argument will be the
result otherwise Third argument will be the result
/*Checking MALE or Female Using Case */
DECLARE @Gender INT = 1
SELECT CASE WHEN @Gender = 1 THEN 'MALE' ELSE 'FEMALE' END AS Gender
Result : MALE
/*Checking MALE or Female Using IF ELSE */
DECLARE @Gender INT = 1
IF(@Gender = 1)
BEGIN
SELECT 'Male' Gender
END
ELSE
BEGIN
SELECT 'Female' Gender
END
Result : MALE
/*Checking MALE or Female Using IIF */
DECLARE @Gender INT = 1
SELECT iif(@Gender = 1, 'MALE','FEMALE') AS Gender
Result : MALE
/*Checking Multivalue using IIF */
DECLARE @NAME Varchar(50) = 'SQL1'
SELECT IIF(@NAME IN ('SQL','ORACLE'),
'DATABASE', 'NOTE IN LIST' )
Result : NOTE IN LIST