This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2012 introduced a new logical function called “IIF” and here, I am going to share basic syntax and use of this function.
The primary purpose of this function is to check the Boolean condition. If the condition is true, it returns a true value. Otherwise, it returns the false value.
Using this function, you can validate and return value in a single line of code and don’t require to use IF-ELSE.
The Syntax of IIF:
1 |
IIF ( boolean_expression, true_value, false_value ) |
Example ONE:
1 2 3 4 5 6 7 |
DECLARE @no1 int = 808; DECLARE @no2 int = 1600; SELECT IIF ( @no1 > @no2, 'TRUE', 'FALSE' ) AS Result; Result ------- FALSE |
Example TWO:
1 2 3 4 5 |
DECLARE @no1 int = 808; DECLARE @no2 int = 1600; SELECT IIF ( @no1 > @no2, NULL, NULL ) AS Result; --Result: Error (Both NULL constant is not allowed) |
Example THREE:
1 2 3 4 5 6 7 |
DECLARE @no1 int = 808; DECLARE @no2 int = 1600; SELECT IIF ( @no1 > @no2, NULL, 'Else' ) AS Result; Result ------ Else |
Example of Nested IIF:
1 2 3 4 5 6 |
DECLARE @Score int = 88; SELECT IIF ( @Score> 90, 'A Grade', IIF(@Score>=80 And @Score<=90,'B Grade','C Grade')) AS Result; Result ------- B Grade |