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

The information in this article applies to:

Microsoft SQL Server 6.0
Microsoft SQL Server 6.5

This article was previously published under Q152353

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

A Select from MyView may cause the client to access violate.

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.

Modification Type:	Major	Last Reviewed:	10/16/2003
Keywords:	kbBug kbfix kbnetwork kbProgramming KB152353