Using IIF In TSQL

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

 

No comments:

Post a Comment