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

avatar image

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

avatar image

Fatherjack ♦♦
43.8k 79 101 118

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 ♦♦
  • 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


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

avatar image

ThomasRushton ♦♦
42.3k 20 57 53

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

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jun 25, 2010 at 10:32 PM

Seen: 1382 times

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

Copyright 2018 Redgate Software. Privacy Policy