transactions in stored procedures using cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

transactions in stored procedures using cursor

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-23 : 09:06:38

Alex Donald writes "Hi,
My question is how to structure correctly Transaction execution when INSERT/UPDATE is performed within Cursor loop or
through nested stored procedures

Currently I check for @@error before each FETCH NEXT FROM, if true ROLLBACK TRAN and CLOSE/DEALLOCATE Cursor

Thanks"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-23 : 09:41:02

1
For insert and Update you can use Set based Approaches

Insert

Insert into table2(columns) Select columns from table1

Update

Update T1
set col=T2.col
from Table1 T1 inner join Table2 T2
on T1.keycol=T2.keycol

2
For error handling refer this
http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources