Microsoft KB Archive/217032

= BUG: DOC: Variable Arguments not Refreshed After Cursor is Declared =

Article ID: 217032

Article Last Modified on 11/4/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



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