CODEWITHSUNDEEP
sqlsql-servert-sql
Lieven Cardoen
Lieven Cardoen

Reputation: 25959

Result of T-SQL Cursor changes at runtime

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...

Upvotes: 2

Views: 231

Answers (2)

Kendrick
Kendrick

Reputation: 3787

If you have a timestamp for "UserCreated" then you can filter your cursor by anything created before the current time.

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135021

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

Upvotes: 9

Related Questions