IIF returns a value from two values based on a boolean expression.
IIF is a shorthand for a CASE expression.
This example returns one of two values by checking a boolean expression.
If true, the first value is returned, otherwise the second value.
SELECT IIF(15 % 2 = 0, 'Even', 'Odd') AS Result
| Result |
|---|
| Odd |
Alternatively, the modulus operator (%) can also test whether a number (15) is even or odd.
Syntax of the IIF function.
IIF(boolean-expression, true-value, false-value)
boolean-expression -- a boolean expression to be evaluated. If this is not a boolean expression, an error is returned.
true-value -- a value or column to return if the boolean-expression result to true.
false-value -- a value or column to return if the boolean-expression result to false.
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
SELECT ProductName, UnitPrice,
IIF(UnitPrice < 50, 'Cheap', 'Expensive') AS Category
FROM Product
ORDER BY ProductName
| ProductName | Unit Price | Category |
|---|---|---|
| Alice Mutton | 39.00 | Cheap |
| Aniseed Syrup | 10.00 | Cheap |
| Boston Crab Meat | 18.40 | Cheap |
| Camembert Pierrot | 34.00 | Cheap |
| Carnarvon Tigers | 62.50 | Expensive |
| Chai | 18.00 | Cheap |
 |
||
| SUPPLIER |
|---|
| Id |
| CompanyName |
| ContactName |
| City |
| Country |
| Phone |
| Fax |
SELECT CompanyName,
IIF(Fax IS NOT NULL,
Fax, 'Phone: ' + Phone) AS Contact
FROM Supplier
| CompanyName | Contact |
|---|---|
| Exotic Liquids | Phone: (171) 555-2222 |
| New Orleans Cajun Delights | Phone: (100) 555-4822 |
| Grandma Kelly's Homestead | (313) 555-3349 |
| Tokyo Traders | Phone: (03) 3555-5011 |
| Cooperativa de Quesos 'Las Cabras' | Phone: (98) 598 76 54 |
|
|
