Microsoft KB Archive/106133

{|
 * width="100%"|

BUG: IF Statement with Two EXISTS Separated by OR Clause

 * }

Q106133

-

The information in this article applies to:


 * Microsoft SQL Server version 4.2x

-

BUG# NT: 544 (4.2)

SYMPTOMS
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.

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

WORKAROUND
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"

STATUS
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