exception handling - best practices

Transact-SQL

two_a_day


hi all,

not sure this is the correct section to post this, but couldn't find a more appropriate one.

we are debating on sql exception handling usage and we could'nt 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_some_constraint_that_is_about_to_be_violated t
values a,b,c
end try

begin catch
IF (error_number() == 2627)
return 1000 -- 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_some_constraint_that_is_about_to_be_violated t
WHERE x=a AND y=b AND z=c))
insert into table_with_some_constraint_that_is_about_to_be_violated t
values a,b,c
ELSE
return 1000
end try

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

thanks in advance

Top

Re: exception handling - best practices

Arnie Rowland


It is a delimma.

Constraint failures cause block failure and unfortunately, cannot be handled by TRY...CATCH.

On the other hand, IF EXISTS causes two database hits (twice the network traffic) for every insert action. And it doesn't completely protect against the possibility that a conflicting row is not created by another user between the check and the insert.

I often see that the most used option is to allow the application to handle the constraint error -it is passed back to the connection object.

When it is a situation of inserting a new row or updating an existing row, I have often issued the UPDATE command, then check the @@ROWCOUNT and if it is zero, then do the insert.






Top

Re: exception handling - best practices

two_a_day

thanks arnie.

Constraint failures cause block failure and unfortunately, cannot be handled by TRY...CATCH.

I didn't understand what you mean by this. constraint errors cause exception and can be handled by try-catch block. That is actually what we are currently doing





Top

Re: exception handling - best practices

Chris Howarth

Rather than using IF EXISTS, you could check for the existance of a duplicate row within the INSERT statement, see below. The values to be inserted are used to form a derived table.

IMO this is wasteful of resources as the validation has to be performed twice - once within the query and again by the unique constraint, but it is another option for you to persue.

Chris

Code Snippet

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

DROP TABLE #tmp

GO

CREATE TABLE #tmp (a INT, b INT, c INT)

GO

--Create a constraint

CREATE UNIQUE CLUSTERED INDEX [PK_#tmp] ON #tmp (a, b, c)

GO

--The values we wish to insert

DECLARE @a INT SET @a = 1

DECLARE @b INT SET @b = 2

DECLARE @c INT SET @c = 3

--Insert the first row

INSERT INTO #tmp (a, b, c)

VALUES (@a, @b, @c)

--Insert the second row, but only if a row

--containing the values doesn't already exist

INSERT INTO #tmp (a, b, c)

SELECT t.a,

t.b,

t.c

FROM (

SELECT @a AS a,

@b AS b,

@c AS c

) t

WHERE NOT EXISTS ( SELECT 1

FROM #tmp mt

WHERE mt.a = t.a

AND mt.b = t.b

AND mt.c = t.c )

--Check @@ROWCOUNT to determine whether to raise an error

SELECT @@ROWCOUNT





Top

Re: exception handling - best practices

Arnie Rowland

Sorry, that comment was a response to a different question, and should not have been in this thread. My regrets for any confusion.

Constraint failures cause block failure and unfortunately, cannot be handled by TRY...CATCH.

Let me emphasize that I would agree with your collegue that suggested handling the issue in TRY...CATCH.

I would not waste the resources for a 'double hit' with the 'IF EXISTS' possibilities. The only exception to that for me is switching between an UPDATE and an INSERT as I indicated earlier. You could also easily handle the UPDATE or INSERT using TRY...CATCH -but in my opinion, it is not an ERROR condition, but a business logic 'decision'.





Top