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

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.