Result of T-SQL Cursor changes at runtime

Question

Is there a way to prevent that a Cursor changes at runtime. If I have a cursor that iterates over all the users and meanwhile, in the processing of each user, I create some additional users, then the Cursor will also iterate over the newly created users...

Answer 1

Your cursor needs to be INSENSITIVE or STATIC

From BOL: http://msdn.microsoft.com/en-us/library/ms180169.aspx

INSENSITIVE

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. When ISO syntax is used, if INSENSITIVE is omitted, committed deletes and updates made to the underlying tables (by any user) are reflected in subsequent fetches.

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.

However I would still recommend using a SET based solution