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.




Related Articles :

  • No Related Articles Found

8 Responses to “How can you use IIf in Transact-SQL?”

  1. Um, IIF does exist in T-SQL. From the SQL Server help:

    IIf
    Returns one of two numeric or string values determined by a logical test.

    Syntax
    Numeric
    IIf(«Logical Expression», «Numeric Expression1», «Numeric Expression2»)

    This function returns «Numeric Expression1» if «Logical Expression» evaluates to TRUE, otherwise returns «Numeric Expression2».

    String
    IIf(«Logical Expression», «String Expression1», «String Expression2»)

    This function returns «String Expression1» if «Logical Expression» evaluates to TRUE, otherwise returns «String Expression2».

    Remarks
    «Logical Expression» is considered to be FALSE only if its value is zero. Any other value is interpreted as TRUE.

    The Iif function is not recommended for creating a set of members based on search criteria. Instead, use the Filter function to evaluate each member in a specified set against a logical expression and return a subset of members.

    Examples
    Numeric
    The following example returns 0 if Measures.CurrentMember is an empty cell, 1 otherwise:

    IIf(IsEmpty(Measures.CurrentMember), 0, 1)

    String
    The following string returns the string “Yes” if Measures.CurrentMember is an empty cell, the string, “No” otherwise:

    IIf(IsEmpty(Measures.CurrentMember), “Yes”, “No”)

  2. Hi Anonymous,
    Um,I suppose you would contend that the OP, SSPADMIN, did not get the error msg he saw.

    It turns out that while IIF() *is* a function in Access, it is not a defined function usable in-line in a SELECT statement.

    Take out the “=” to get the parser to go a bit further, for example:

    select ‘x’ as f1, IIF( 1, ‘a’, ‘b’) as f3;

    And this is the result:

    Msg 195, Level 15, State 10, Line 1
    ‘IIF’ is not a recognized built-in function name.

    And we’re back to coding the CASE statement.

  3. Dear Anonymous,

    IIF does not exist in SQL. The help that you did a copy and paste on is for MDX a totally different language from SQL.

    The original article is quite helpful for people who are more familiar with another language rather than SQL.

  4. I have columms data, vector and name. The data values change according to vector for every name and there is a list of names.. How can I calculate
    (Data(vector=1) + data(vector=2)*2 + data(vector=3)*3) / Data(vector=2) + data(vector=3)

    Thanks for the help

  5. Thank you – Worked a treat. Was attempting to basically say if a lookup is null then use the result of one field else return the lookup result in SSRS. Cheers.

  6. http://msdn.microsoft.com/en-us/library/hh213574.aspx

    IIF (Transact-SQL)

    DECLARE @a int = 45;
    DECLARE @b int = 40;
    SELECT IIF ( @a > @b, ‘TRUE’, ‘FALSE’ ) AS Result;

    Not sure if it works in a case though, thats where i got stuck…

  7. I’ve had the same problem when trying to find pattern “xyz” in the “Name” field
    SELECT IIF(PATINDEX(‘%xyz%’, Name) > 0, 1, 0) as X FROM ….
    No good, so I turned it into
    SELECT CASE WHEN PATINDEX(‘%xyz%’, Name) > 0 THEN 1 ELSE 0 END as X FROM…
    Worked like a charm.
    happy coding, sean

  8. There’s a fresh comment from a contributor in the mentioned MSDN article:

    This article is missing the essential detail that IIF is supported starting with SQL Server 2012, aka Denali.
    http://blog.sqlauthority.com/2011/09/10/sql-server-denali-logical-function-iif-a-quick-introduction/

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |