INF: Emulating the iif() Function in Transact-SQL

ID: Q124243

The information in this article applies to:

SUMMARY

Microsoft SQL Server version 6.5 supports the powerful ANSI SQL-92 CASE expression which allows SQL expressions to be simplified for conditional values. For more information on how to use the CASE command, see the Microsoft Books OnLine for SQL Server version 6.5.

Prior to SQL Server version 6.5, the Transact-SQL language did not have an equivalent to CASE or the function iif() (sometimes called Immediate If or Inline If) that is available in other programming languages, including Microsoft Access and Microsoft FoxPro. This article provides information on how to produce similar results using the Transact-SQL features available in versions of SQL Server prior to version 6.5.


MORE INFORMATION

Some SQL implementations allow statements like the following: 


   select name, iif( score > 80, "Pass", "Fail" ) from exams

The iif() expression evaluates to its second argument if its first argument is true, otherwise, to its third argument. Porting code like this to Transact-SQL can be accomplished in several ways:
  1. In SQL Server version 6.0, a CASE expression can be used wherever an expression can be used. The statement above could be written in SQL Server version 6.0 as:
    
      select name,
        case
          when score > 80 then "Pass"
          else "Fail"
        end
      from exams
    See the Microsoft SQL Server Books Online and the "Transact-SQL Reference" manual for more examples of using the new CASE expression of version 6.0.


  2. Build the iif() logic into the front-end application using Transact-SQL queries to return only the raw column data.


  3. Use a JOIN to a lookup table. This works best if the columns included in the first argument of the iif() take on only a few values. For the example above, you could construct a grading table:
    
   score      grade
   -----      -----
   1          'Fail'
   2          'Fail'
   3          'Fail'
   ...
   99         'Pass'
   100        'Pass'

    Then the following query would be equivalent to the example: 
    
   select exams.name, grading.grade from exams, grading
   where exams.score = grading.score



  4. Use two SELECTs. Each returns as a constant one of the possible values of the iif() expression:
    
   select name, 'Fail' from exams where score <= 80
   union all
   select name, 'Pass' from exams where score > 80


  5. Use the ABS, SIGN, and SUBSTRING functions. For example:
    
   select
     name,
     SUBSTRING('FailPass', 1 + 4 * SIGN((score - 80) + ABS(score - 80)), 4)
     from exams


Additional query words: sql6 Windows NT 


Keywords          : kbprg SSrvProg SSrvTran_SQL 
Version           : 4.2 | 4.2 6.0
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: April 17, 1999