FIX: CASE Expression in WHERE Clause of SELECT May Cause AVLast reviewed: April 9, 1997Article ID: Q156736 |
The information in this article applies to:
SYMPTOMSA 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 BrokenOther processes on the server are not affected.
WORKAROUNDThis 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.
STATUSMicrosoft 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 INFORMATIONThe 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 |
Additional query words:
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |