x

SQL Cursor

Hi All,

I need one basic information regarding to SQl Cursor. Let I have created a cursor on table which have 10000 records. The cursor will execute as per their functionality and after that it will update few records.

If in any case i will cancel this query where 500 or more records will get fetch and update table. What will happen to next is cursor get rollback or not if I cancel my query in mid of execution?

Regards, xyz
more ▼

asked Sep 24, 2012 at 06:36 PM in Default

shalabh2108 gravatar image

shalabh2108
40 3 3 3

Just to clarify, you are asking if you have a cursor that has already updated 500 records and you cancel the execution, what happens to those 500 records in terms of rollback right? Is that correct? If so, it can depend on how your are writing the cursor. Can you post your code?
Sep 24, 2012 at 08:14 PM JohnM
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
Typically a cursor is row by row, so you would be executing an update statement 500 times to update those 500 records. If you cancel the job on record 501, it would roll backup record 501, not the 500 others that had already been committed. There are exceptions if the cursor is embedded into another transaction that the who thing could be rolled back. As a hunch I don't think that is your case. If you could post the code, you could get a solid answer.
more ▼

answered Sep 25, 2012 at 04:16 AM

Tim gravatar image

Tim
36.4k 35 41 139

+1. My thoughts exactly.
Sep 25, 2012 at 11:50 AM JohnM
(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:

x716
x58

asked: Sep 24, 2012 at 06:36 PM

Seen: 572 times

Last Updated: Sep 25, 2012 at 11:50 AM