Transaction & Error Handling - SQL Server Forums

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

Transact-SQL (2000)

Transaction & Error Handling

Author

Topic

anandc
Starting Member

20 Posts

Posted - 2006-06-19 : 06:37:30

[code]
BEGIN TRANSACTION
DELETE FROM trd_CommTransPayout WHERE intTransId = @intTransId
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN(1)
END

UPDATE tr_CommTrans
SET TrdDt = @dtmTradeDt,
CommTrailInd = @strCommTrailInd,
Comments = @strComments
WHERE intTransId = @intTransId

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN(1)
END

COMMIT TRANSACTION
[/code]

Do I need to check IF @@ERROR <> 0 after everry DELETE / UPDATE in transaction??

- Anand

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-19 : 06:54:48

Yes
and remember that some errors are not trappable and will abort the connection.

If you have v2005 you can use a try catch block instead.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-19 : 07:19:24

http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-06-19 : 14:28:04

"Do I need to check IF @@ERROR <> 0 after everry DELETE / UPDATE in transaction??"

Have a look at

SET XACT_ABORT ON

which may help you to get your Sprocs to fail if something goes wrong. We rely on that for when we miss a test on @@ERROR

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources