Opening cursor after closing does not seem to requery

From: mberry (lionheart1159_at_yahoo.com)
Date: 03/12/04 

Date: Fri, 12 Mar 2004 11:22:12 -0500

Okay, I'm a newbie, so any help would be appreciated.

I declare a cursor, then enter a loop. Inside the loop, I open and fetch,
and everything works wonderfully until I close the cursor. When I re-open
the cursor (I do this because I want it to re-query per the declaration), it
opens at the same row as the initial opening, even though the variable in
the query part of the cusor declaration has clearly changed.

Declare myCursor Cursor
           For Select col1_name
                            , col2_name
                            , col3_name
           From myTable
          Where col3 = @other_var
          Order By col1_name DESC
         For Update of col2_name

While (@@FETCH_STATUS = 0)
Begin
     Open myCursor
     Fetch myCursor Into @col1_var, @col2_var, @col3_var

    While (@@FETCH_STATUS = 0)
    Begin
        parse through the table, doing stuff that could probably be done
easier with correlated subqueries
        eventually, change @other_var
    End

    close myCursor
End

After I close myCursor, and re-open it at the top of the loop, I think it
should open pointing to the row that is qualified by the new value of
@other_var, but it does not. I'm sure I am doing something wrong. I have
succeeded by deallocating the cursor, and moving the myCursor declaraton
inside the loop, but this seems to be very inefficient.

Thanks in advance for any help!

Michael

Relevant Pages

  • Re: Cursor loop
    ... I've created a stored procedure that loops through a cursor, ... DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods ... The problem is that this loop only executes one time, ...
    (comp.databases.ms-sqlserver)
  • Re: ORA-06550: declaring cursor inside "begin/end" structure of PL/SQL block
    ... > This is a newbie question so please don't be too annoyed if the ... > I've written PL/SQL which gets a lists of values into a cursor. ... > list of values is then used in a loop to create another cursor ... > DECLARE ...
    (comp.databases.oracle.server)
  • Re: ORA-06550: declaring cursor inside "begin/end" structure of PL/SQL block
    ... > This is a newbie question so please don't be too annoyed if the ... > I've written PL/SQL which gets a lists of values into a cursor. ... > list of values is then used in a loop to create another cursor ... > DECLARE ...
    (comp.databases.oracle.server)
  • Loop with Read Only cursor?
    ... loop, a few days ago. ... but I don't know how to declare a read only cursor. ... I had 2 columns - a RowNum col and ...
    (microsoft.public.inetserver.asp.general)
  • Fetch Into Within a User Defined Function
    ... I have a user defined function that opens a cursor and builds a delimited ... DECLARE @List As varchar ... -- Perform the first fetch. ...
    (microsoft.public.sqlserver.programming)