Microsoft KB Archive/156736

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

Article ID: 156736

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q156736



BUG #: 15877 (6.5)



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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.