BUG: Stored Procedures May Retain Variable Value Between Executions (278486)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
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)
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbbug kbpending kbSQLServ2000sp1fix KB278486
©2003 Microsoft Corporation. All rights reserved.