x

Problem in iteration of Cursor

I am defining a cursor with Table1 with condition that column1 = Value .
In this cursor i am updating status of existing record
and add new record in same table but with column1 = value (Specified in Where Clause of cursor Definition)
This result in updation in cursor new recored also gets in the 'fetch Next statment'
what Should i do to prevent new record in cursor.
more ▼

asked Jun 25, 2010 at 10:32 PM in Default

rohanrpatil gravatar image

rohanrpatil
11 2 2 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
My recommendation would be to remove the cursor and implement a set-based solution, in the majority of cases it will be much faster and more reliable. Can you post any of your cursor and the ddl of the tables you are working with so we can help?
more ▼

answered Jun 26, 2010 at 01:21 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

And there you have it. Two approaches - I answer the question, and you question the questioner. But you're right, hence +1.
Jun 26, 2010 at 02:32 AM ThomasRushton ♦
+1 for obvious reasons. ;-)
Jun 30, 2010 at 03:53 PM Jeff Moden
+1. Few days ago the article by R Barry Young titled "There Must Be 15 Ways To Lose Your Cursors" was re-published on SSC site. This is a very good read for anyone asking cursor-related questions: http://www.sqlservercentral.com/articles/T-SQL/66097/
Jun 30, 2010 at 04:12 PM Oleg
@Oleg - good linkage there. I like the way that the repost of Part 2 of the article is from the future (republished on 2010-07-02, and it's currently, erm, 2010-07-01)!
Jun 30, 2010 at 10:37 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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

Make your cursor into an "INSENSITIVE" cursor (if you're using the ISO syntax):

DECLARE cursorname INSENSITIVE FOR... (rest of cursor definition as before)

or "STATIC" cursor if you're using the T-SQL enhanced cursor definition:

DECLARE cursorname CURSOR STATIC FOR ... (rest of cursor definition as before)
more ▼

answered Jun 26, 2010 at 12:09 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x59
x4

asked: Jun 25, 2010 at 10:32 PM

Seen: 1191 times

Last Updated: Jun 26, 2010 at 01:19 AM