Start position for a reused t- sql cursor?

Question

I'm working on a stored procedure that uses a cursor on a temporary table (I have read a bit about why cursors are undesirable, but in this situation I believe I still need to use one).

In my procedure I need to step through the rows of the table twice.

Having declared the cursor, already stepped through the temporary table once and closed the cursor, would the position of the cursor remain at the end of the table when re-opened or does it reposition itself to the initial starting position (ie: before the first row)?

Alternatively, to reposition the cursor must I do a 'FETCH FIRST' before stepping through again?

Am I right to assume the 'cost' of doing this repositioning and reusing the cursor would be less than deallocating and reallocating the cursor?

Geomancy

Answer 1

The allocation and deallocation costs are trivial. The "badness" of a cursor comes from the fact that you're not interacting with the DB in an optimal manner, not from any particular overhead of creating or disposing of a cursor.

I don't think the behavior of the position of a closed and reopened cursor is documented, so you shouldn't depend on it acting in any given way. So, you should reposition it yourself when you start using it again.

And, it's possible that what you're doing might be doable without a cursor. I'd consider asking (a different) question related to that if I were you. :)

Holusbolus

thanks, I only just discovered the reasons as to why cursors are not always desirable yesterday when seaching for an answer to my question (I'm a new graduate and at university we were only taught cursors..) I plan on reading up on it a bit more before I post any new questions, but I do suspect that my use of cursors could be replaced - I just need to understand the alternatives a bit better first. :) Be that as it may, I'm still interested to find out the state of a cursor after it has completed iterating through a recordset. I find it curious that this hasn't been documented. – Geomancy