Knowledge Base

FIX: CASE Expression in WHERE Clause of SELECT May Cause AV

Article ID: 156736

Article Last Modified on 10/3/2003

Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q156736

SYMPTOMS

A CASE expression in the WHERE clause of a SELECT statement may cause a handled access violation (AV).

The client's connection to the server is broken when the access violation occurs. The client will receive the following message:

DB-Library Process Dead - Connection Broken

Other processes on the server are not affected.

WORKAROUND

This problem only occurs if the variable into which the parameter values are passed to the stored procedure is used in the CASE expression of the SELECT statement. Therefore, the problem can be avoided by transferring the parameter value to another variable that is defined inside the stored procedure, and then using that second variable in the CASE expression. See the MORE INFORMATION section of this article for an example.

STATUS

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

MORE INFORMATION

The following example does not cause an access violation, because the value in "@my_flag" is passed into the "@pass" variable, which is used in the CASE expression:

   CREATE PROCEDURE asp_My_Test
      @my_flag char(1)
   as
   declare @pass char(1)
   select @pass = @my_flag
   begin
            select *
            from
               pubs..authors
            where
               au_lname LIKE case @pass
                                     when '1' then '[A-D]%'
                                     when '2' then '[E-Z]%'
                                     else '%'
                                  end
   end
   go
   exec asp_My_Test '2'
   go

Keywords: kbbug kbfix kbusage KB156736