Error handling in stored procedures

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)

Error handling in stored procedures

Author

Topic

gpiva
Starting Member

47 Posts

Posted - 2005-08-29 : 23:12:39

Hi Guys I need some advice on a tedius topic
I have some sp that incapsulate business logic and transactions
I read on an article that if you dont use error handling in the store procedure you will be never sure that the transsaction was successfull, to make it more clare if I run a sp like

sp_submitApplication

begin transaction

insert into QUA
insert into LAB
insert into CAB

commit transaction

WITHOUT an appropriate @error handling sql server WILL commit in any case my transaction even if 'insert into LAB' didn't do the job due for an error.

is this TRUE??

Any help will be appreciate.

Carlo

Kristen
Test

22859 Posts

Posted - 2005-08-30 : 03:14:38

I wish I knew for sure!

I reckon

SET XACT_ABORT ON

would do for your example. It will cause any error within the SProc to abort the whole Sproc.

But if you do:

CREATE PROCEDURE usp_Wrapper
...
AS
SET XACT_ABORT ON
...
EXEC sp_submitApplication
...

then a failure in sp_submitApplication will NOT cause usp_Wrapper to fail.

I would certainly want a check for @@ROWCOUNT after every insert to make sure that the expected number of rows was inserted, so whilst you are at it checking @@ERROR wouldn't be a bad idea!

Kristen

gpiva
Starting Member

47 Posts

Posted - 2005-08-30 : 03:43:28

Thank you for your reply,
I was thinking to be safe using XACT_ABORT ON since I found those articles

http://www.sommarskog.se/error-handling-II.html#presumptions
http://www.sommarskog.se/error-handling-II.html#XACT_ABORT

after reading I posted on this forum to see other opinions,
from my point of view (considering my scenario) XACT_ABORT ON
is safe I agree, but after all raising error is the best way.

Cheers,

Carlo.

Kristen
Test

22859 Posts

Posted - 2005-08-30 : 09:10:01

"but after all raising error is the best way."

I can't argue with that! Getting into the habit of putting the error handling in has to be A Good Thing!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources