FIX: Select with CASE Statement Inside View Can Cause Client AV

• Microsoft SQL Server versions 6.0 and 6.5

SYMPTOMS

A View that has a Select statement with a CASE construct that does a sub- select with an IN clause may cause the client to access violate (AV) during a Select from the View.

The following sample is the problem View definition:

   create view MyView as
   Select   Column1, Column2=
         Case
           When SomeColumn In
            (Select  SomeOtherColumn
              From   SomeOtherTable)
             Then SomeValue
           Else Null
         End
   From  MyTable

WORKAROUND

Try to avoid the CASE construct inside a View Definition or perform some pre-processing for the CASE logic before the View Definition by using the IF-ELSE construct and Temporary Tables, and then define the View on the Temporary Table.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.