IIF() Function in SQL Server

The SQL Server IIF() function has three parameters. IIF() function judges or evaluates the first parameter and returns the second parameters if the first parameters is true; otherwise, it returns the third parameters. IIF() function used in SQL Server to add if-else logic to queries.>

Syntax :

IIF(boolean_value, true_value, false_value)

Parameters used in Syntax:

• boolean_value –
  It is a value to be judged. It must be a valid boolean value, or the function will raise an error.
• true_value –
  It is the value to be resulted if the boolean_value to true.
• false_value –
  It is the value to be resulted if the boolean_value to false.

Fact to know: The IIF() function is similar to a CASE expression – 

CASE  
   WHEN boolean_expression  
       THEN true_value
   ELSE
       false_value
END

Example-1 :
To use the IIF() function to check if 40 < 60 :

SELECT  IIF(40 < 60, 'True', 'False') AS Result ; 

Output : Result True

Let us assume we have below sample table named “Geektable“:

G_id	G_status
1	3
2	2
3	4
4	2
5	3
6	1
7	2
8	1
9	4
10	1
11	4
12	3
13	1
14	3

Example-2 :
To use IIF() function with table column.
Below example uses IIF()function within IIF() functions:

SELECT    
   IIF(G_status = 1, ‘Waiting’,  
       IIF(G_status=2, ‘InProgress’,
           IIF(G_status=3, ‘Rejected’,
               IIF(G_status=4, ‘Completed’)
           )
       )
   ) AS Status,

   COUNT(G_id) AS Count
FROM Geektable
GROUP BY G_status ; 

Output :

Status	Count
Waiting	4
InProgress	3
Rejected	4
Completed	3

Example-3 :
To use IIF() function with aggregate functions.
Below example uses IIF()function with the SUM() function:

SELECT    
   SUM(IIF(G_status = 1, 1, 0)) AS ‘Waiting’,  
   SUM(IIF(G_status = 2, 1, 0)) AS ‘InProgress’,  
   SUM(IIF(G_status = 3, 1, 0)) AS ‘Rejected’,  
   SUM(IIF(G_status = 4, 1, 0)) AS ‘Completed’,  
   COUNT(*) AS Total

FROM  Geektable;

Output :
Here, the IIF() function results 1 or 0 if the status is matched. The SUM() function results the number of each status.

Waiting	InProgress	Rejected	Completed	Total
4	3	4	3	15

My Personal Notes arrow_drop_up

Save