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

 Last reviewed: October 9, 1997
Article ID: Q124243

The information in this article applies to:
  • Microsoft SQL Server, version 4.2 and 6.0
  • Microsoft SQL Server, version 4.2 for OS/2

SUMMARY

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


  • 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


  • 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          : SSrvProg SSrvTran_SQL kbprg
    
Version           : 4.2 | 4.2 6.0
    
Platform          : OS/2 WINDOWS
    

    
    

    

    
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

    

    

    
	
	Last reviewed:  October 9, 1997
	
    
	© 1998 Microsoft Corporation. All rights reserved. Terms of Use.