BUG: DOC: Variable Arguments not Refreshed After Cursor is Declared (217032)

The information in this article applies to:

Microsoft SQL Server 7.0

This article was previously published under Q217032

BUG #: 54210 (SQLBUG_70)
BUG #: 55384 (SQLBUG_70)

SYMPTOMS

A behavior change has occurred in SQL 7.0 for when a cursor is declared that contains a variable argument in the where clause. In SQL Server 6.5, the query plan is created at the time the cursor is opened. Therefore, if the cursor is closed and not deallocated and the variable in the where clause is updated, upon re-opening the cursor, the resultset is refreshed based off the current value of the variable.

In SQL Server 7.0, variables used in a cursor declaration cannot update their value once the cursor declaration occurs. The variable in the where clause is not dynamically resolved again when a cursor has been declared. Turning the database Compatibility mode to 65 does not change the behavior.

WORKAROUND

To workaround this problem, you can either:

Initialize the variable before the declaration of the cursor. Close and deallocate the cursor, initialize the variable and declare and open the cursor for the next iteration.

NOTE: A sample of this is provided in the SQL Server Books Online.

-or-
Put the cursor declaration, open, and fetch operation in a stored procedure and call the stored procedure passing the variable as an argument for the where clause.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

The following pseudocode example may illustrate the change in behavior better between SQL 6.5 and SQL 7.0:

use pubs
go

create proc CurTest as
declare @id varchar(10)
select @id='BU1032'
declare cur cursor for select * from titles where title_id=@id
open cur
fetch cur
close cur
select @id='BU1111'
open cur
fetch cur
close cur
deallocate cur

go

exec CurTest

REFERENCES

SQL Server Books Online, "DECLARE CURSOR (T-SQL)"

Modification Type:	Major	Last Reviewed:	11/4/2002
Keywords:	kbBug kbdocerr KB217032 kbAudDeveloper