CODEWITHSUNDEEP
sql-servercursor
quillbreaker
quillbreaker

Reputation: 6215

t-sql cursors : does "fetch first from" regenerate the contents of the cursor?

I have a block of someone else's code that does essentially this:

declare X cursor static for select * from stuff
open cursor X
fetch next from X
while @@fetch_status = 0
begin
  do stuff
  fetch next from X
end

fetch first from X
while @@fetch_status = 0
begin
  do different stuff
  fetch next from X
end

To get it out of the way, yes, I know cursors are bad / no longer in fashion / etc. I didn't write it. I just have to fix it. Now to the question:

If the data behind the underlying cursor changes between the first loop and the second loop, could the second cursor loop see different data than the first cursor loop? Or does the static qualifier prevent that?

Upvotes: 3

Views: 2406

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

The data retrieved by the cursor will not change. As documented here:

STATIC

Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

Upvotes: 4

Related Questions