Transactions in Cursors

[koooob]

Hi, hope somebody can help me out here.

I'm writing an import script that goes through a result set using a cursor. This executes several inserts and updates depending on the data in that row, adds an id to another table to show I've dealt with that record then goes onto the next record. What I want to do is say - perform step 1, 2, 3, if there is an error with any of them, rollback, then move onto the next record. I'm not really sure how to do this inside a cursor though. Anyone done something like this before?

Here an rough outline of what I'm trying to achieve:

get record set
get next record
begin transaction
if field1=something
begin
insert a record
end
insert a record
update a record
insert another record
exec something
insert current record ID to imported list
if no errors in above
commit transaction
else
rollback
loop - get next record

I'd be grateful for any advice!

 

[onpnt]

What version of SQL Server are you on?


_{____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[koooob]

Sorry should have said - 2000

 

[onpnt]

The task can be completed fairly easily with DTS then. Avoid cursors at great lengths of course. This applies to scheduled import jobs as well.

I would suggest posting your question to forum961

You can do this with a lengthy SQL script though if DTS is not something you want to do. Although I wouldn't recomemnd it sense the ETL services is where this task belongs. If that is the case however in using SQL for a solution, you'll need to get something together first and if you get to a point you're having problems post a specific problem.




_{____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[koooob]

OK thanks, will do

Kuba