Microsoft KB Archive/124243

{|
 * width="100%"|

INFO: Emulating the iif Function in Transact-SQL

 * }

Q124243

-

The information in this article applies to:


 * Microsoft SQL Server versions 4.2x, 6.0

-

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:

  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.  Build the iif logic into the front-end application using Transact-SQL queries to return only the raw column data.  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 </li></ol>

Additional query words: sql6 Windows NT

Keywords : kbprogramming

Issue type : kbinfo

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600 kbSQLServ420OS2