Using Begin and Commin Tran, what will/won't it do

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 2005 Forums

Transact-SQL (2005)

Using Begin and Commin Tran, what will/won't it do

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-08 : 15:51:07

Hi,

If you simply do a:

BEGIN TRAN

EXEC sp_some_sproc @id, @id2

IF(@test = 1)
EXEC sp_blah2

COMMIT TRAN

Will this actually roll things back if there is an error of some sort? From what I understand you have to put @@ERROR statements and then GOTO and then clean things up manually right?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-08 : 16:01:15

Assuming the called SPs don't do any COMMIT or ROLLBACK then if there is a failure you will leave open a transaction. You definately need to make sure all your loose ends are cleaned up and any transactions are either committed or rolled back.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 23:32:33

You need to handle error by giving a TRY..CATCH block and give ROLLBACK TRAN inside CATCH to make sure you dont leave any open transactions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources