Skip to content
Related Articles

Related Articles

Improve Article
IIF() Function in SQL Server
  • Last Updated : 16 Oct, 2020

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 :ResultTrue

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

G_idG_status
13
22
34
42
53
61
72
81
94
101
114
123
131
143

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 :

StatusCount
Waiting4
InProgress3
Rejected4
Completed3

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.

WaitingInProgressRejectedCompletedTotal
434315
My Personal Notes arrow_drop_up
Recommended Articles
Page :