How can you use IIf in Transact-SQL?

 

This is a quite common question. It is usually asked by people arriving at SQL Server with a background in Microsoft Access. They either want to use SQL Server as a backend for their Access project, or they are otherwise upsizing from Access to SQL Server. The answer, however, is usually not much appreciated at first:

There is no IIf in SQL Server's Transact SQL language!

Like it or not, such queries have to be rewritten using the CASE expression. Let's look at a simple example:

SELECT 
 Customers.CustomerID
 , Customers.CompanyName
 , Customers.Country
 , IIf([Country]="Germany","0049 " & [Phone],[Phone]) AS Telefon
FROM 
 Customers

This is a valid query in Access, which evaluates within Access' Northwind sample database whether a Customer is located in Germany or not. If this is the case (pun intended!), it automatically adds the international telephone number for Germany in front of the phone number. If you try to run this in SQL Server's Query Analyzer, you'll get:

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '='.

That's it. The query stops with this error message. So, as was mentioned above, the query has to be rewritten using the CASE expression. That might look something like this:

SELECT 
 Customers.CustomerID
 , Customers.CompanyName
 , Customers.Country
 , CASE
    WHEN Country='Germany' 
     THEN '0049 ' + Phone
     ELSE Phone
     END AS Phone
FROM 
 Customers

This is a valid Transact-SQL query, which SQL Server can understand and execute.

CASE is one of the most powerful commands in the Transact-SQL language. In contrast to IIf, where you only evaluate one logical expression at a time, this limitation does not exist for CASE. Try, for example, to put this in one single IIf expression:

SELECT 
 Customers.CustomerID
 , Customers.CompanyName
 , Customers.Country
 , CASE Country
    WHEN 'Germany' 
     THEN '0049 ' + Phone
    WHEN 'Mexico'
     THEN 'Fiesta ' + Phone
    WHEN 'UK'
     THEN 'Black Pudding (Yuk!) ' + Phone
     ELSE Phone
     END AS Phone
FROM 
 Customers

Don't spent too much time here on the sense of this query, but you will get the idea of what is possible with CASE. And once you are familiar with using CASE, you'll hardly miss IIf anymore.