Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/106133

From BetaArchive Wiki

BUG: IF Statement with Two EXISTS Separated by OR Clause


The information in this article applies to:

  • Microsoft SQL Server version 4.2x

BUG# NT:  544 (4.2) 


If one of the SELECT statements is against an empty tableA query that issues an IF EXISTS statement with an OR clause can process incorrectly. The basic logic looks like:

   If exists (SELECT f1 FROM table1 WHERE f1 = "z") OR
      exists (SELECT f1 FROM table2 WHERE f1 = "a")
         select "TRUE"
         select "FALSE" 

If table1 is empty and table2 has a row that matches the select, the statement should return TRUE; however, it returns FALSE. If a row is added to table1, even though it does not meet the search criteria, the IF EXISTS statement will process correctly.


SQL Server is not correctly evaluating the IF statement when one or more of the tables has no rows.


This problem can be avoided by making sure that both tables contain at least one row. An alternate method is to restructure the IF statement to evaluate one of the EXISTS at a time. In the example given above, it would look like the following:

   If exists (SELECT f1 FROM table1 WHERE f1 = "z")
      select "TRUE"
   else if exists (SELECT f1 FROM table2 WHERE f1 = "a")
      select "TRUE"
      select "FALSE" 


Microsoft has confirmed this to be a problem in SQL Server version 4.2.

Additional query words: transact-SQL Windows NT

Keywords : kbprogramming kbSQLServ
Issue type :
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2

Last Reviewed: November 6, 1999
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.