Microsoft KB Archive/278486

= BUG: Stored Procedures May Retain Variable Value Between Executions =

Article ID: 278486

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q278486



BUG #: 350858 (SHILOH)



SYMPTOMS
In a stored procedure that accepts parameters, if one of the parameters defaults to the value of another parameter, the parameter with the default may retain its value from the previous execution of the stored procedure, instead of being reset to the default.



WORKAROUND
To work around this problem, do one of the following:
 * Supply values for all parameters.
 * Use the WITH RECOMPILE option when you create the stored procedure.
 * Set that parameter's default value to a value that it will never hold, perhaps NULL, and test it in the stored procedure. If that parameter's value is equal to the default, change it to be equal to the parameter that was used as the default in the original stored procedure.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
When you run the following script against SQL Server 2000, the second and fifth executions of the stored procedure return incorrect results: use Northwind go drop procedure pTest go CREATE PROCEDURE pTest @EmployeeID1 INT, @EmployeeID2 INT = @EmployeeID1 AS -- Author: Mattias Beermann [mb@intus.se] PRINT '@EmployeeID1 = ' + convert(char,@EmployeeID1) PRINT '@EmployeeID2 = ' + convert(char,@EmployeeID2) SELECT EmployeeID FROM Employees WHERE EmployeeID BETWEEN @EmployeeID1 AND @EmployeeID2 go EXECUTE pTest 3 EXECUTE pTest 5 EXECUTE pTest 5, 5 EXECUTE pTest 5 EXECUTE pTest 2 EXECUTE pTest 5, 6 go When executed on SQL Server 2000, the script returns the following:  @EmployeeID1 = 3 @EmployeeID2 = 3 EmployeeID --- 3

(1 row(s) affected)

@EmployeeID1 = 5 @EmployeeID2 = 3 EmployeeID ---

(0 row(s) affected)

@EmployeeID1 = 5 @EmployeeID2 = 5 EmployeeID --- 5

(1 row(s) affected)

@EmployeeID1 = 5 @EmployeeID2 = 5 EmployeeID --- 5

(1 row(s) affected)

@EmployeeID1 = 2 @EmployeeID2 = 5 EmployeeID --- 2 3 4 5

(4 row(s) affected)

@EmployeeID1 = 5 @EmployeeID2 = 6 EmployeeID --- 5 6

(2 row(s) affected)

Keywords: kbbug kbsqlserv2000sp1fix kbpending KB278486

-

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

© Microsoft Corporation. All rights reserved.