Microsoft KB Archive/106133
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" else 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" else 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