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

• Microsoft SQL Server version 7.0

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 this to be a bug in the Microsoft products 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:

   Set @var1 = value1
   Declare cursortest Cursor As
    Select * from tblname where fld_name = @var1
   Open Cursortest
   Fetch CursorTest ....
   Close CursorTest  
   Set @var1 = value2
   Open CursorTest
   Fetch CursorTest ...
   Close CursorTest 

REFERENCES

Additional query words: kbbug7.00 SSrvStProc SSrvProg

Keywords : kbdocerr SSrvProg SSrvStProc kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug