Cursors:

[ndba]

Hi:

Here is a scenario and I need to know where is the best place to put a begin transaction in a stored procedure:
I have just put some steps of execution below. I would like to rollback any changes made by the inside stored proc but move on to the next fetch from the outside cursor. I am just not sure where I need to place the begin transaction in the inside cursor and outside cursor. Please advise

Bulk insert
Open cursor
--inside stored proc
Call to another stored procedure
for update (this stored proc has a
cursor within it)
Cursor close
Bulk Update


Thanks in advance

[MAK]

Before even fixing your cursor, dont you think that cursor is too much for this kind of process.

Did you thought about using temp tables instead of cursors.

Cursor is too expensive and poor performance. That too you have 1 cursor in each procedure.

[skhanal]

A ROLLBACK transaction always rolls back all the transactions till the outermost BEGIN TRAN.

So if you put a begin tran in first proc and put another begin tran in second proc and roll back in second proc, all your transactions including the one in first proc get rolled back.

You should consider using save points in your outer proc using SAVE TRANSACTION statement, in this case if you rollback in inner proc, all the transactions upto the save point are rolled back and you can continue with other statements.