Modifying a table a cursor is declared on

Question

So I've created a cursor on a temp table in a stored proc. However, in the @@fetch_status while loop, this temporary table is changed. I would expect that the cursor will see the new entries in the table it was declared on and will continue looping on them. Is this actually the case or does SQL take a snapshot of the table when you declare a cursor?

Answer 1

If you declare the cursor as being DYNAMIC it should see any changes made to a row when you enter and fetch that row. Any changes made post-fetch will, of course, not be visible to you. I don't remember if the default cursor behavior is STATIC or DYNAMIC, so you're just best off declaring it the way you want it to act to be safe.

Note that there are some limitations on what other options can go into a DYNAMIC cursor, see the MSDN page for all the nitty gritty.