sql server exception handling (best practice?)

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)

sql server exception handling (best practice?)

Author

Topic

serhat
Starting Member

2 Posts

Posted - 2007-05-09 : 02:29:33

Hi all,

we are debating on sql exception handling usage and we couldn't have an agreement on whether we should trap constraint exceptions with a try-catch block or check them before executing insert/update/delete statements.

one of my collegues insist that constraint exceptions should be considered in terms of error handling rather than exception handling and should not be handled by sql exception handling mechanism. He suggests to use "if exists" before every insert/update/delete as a constraint check inside a try block, so that only the records which are changed just after the if exists statement had executed may cause exception.

Briefly, it boils down to this:

begin try
insert into table_with_pk_constraint_that_is_about_to_be_violated t
values a,b,c
end try

begin catch
IF (error_number() == 2627)
return 55000 -- our inproc. return code for duplicate.
ELSE
RAISERROR('error_message_here', 11,1)
end catch

OR

begin try
IF (NOT EXISTS( SELECT * FROM table_with_pk_constraint_that_is_about_to_be_violated t
WHERE x=a AND y=b AND z=c))
insert into table_with_pk_constraint_that_is_about_to_be_violated t
values a,b,c
ELSE
return 1000
end try

begin catch
IF (error_number() == 2627)
return 55000 -- our inproc. return code for duplicate.
ELSE
RAISERROR('error_message_here', 11,1)
end catch

thanks in advance

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-05-09 : 02:33:45

>>He suggests to use "if exists" before every insert/update/delete as a constraint check inside a try block

Nice way to duplicate code and resource usage...

Can you give an estimated percentage of violations? (1% or 50%)

DavidM

Production is just another testing cycle

serhat
Starting Member

2 Posts

Posted - 2007-05-09 : 04:46:31

thanks byrmol.

I guess it is something around %5 to %10.

The main argument here is that it is foreseable(if there is a,say, pk constraint, then a violation error might occur in insert/update) and therefore not an exception. Exception should be something that is unexpected. (by the way, I totally disagree this.)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-05-09 : 04:54:53

>>%5 to %10

That could actually be significant depending on the number of transactions.

A PK violation, under "normal" circumstances, is extraordinary and hence my disdain for trying to "help" the database by duplicating the constraint in a procedural fashion.

It comes down to this...

Does the resources needed to "help" the database exceed the resources expected to be used by letting the db throw exceptions?

DavidM

Production is just another testing cycle

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-09 : 06:13:34

www.sommarskog.se/error-handling-I.html
www.sommarskog.se/error-handling-II.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