Contnuing after PK Violation by checking @@ERROR

Last Post 24 Oct 2008 08:29 AM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

22 Oct 2008 09:28 AM
Does this design pattern work in TSQL on SQL 2005?

INSERT into sometable

IF @@error <> 0 –expect 2627 unique constraint violation
UPDATE sometable

Continue doing more stuff…

Here, the code is trying to do an insert, while not knowing if the row already exists in the table. If it does exist, it expects an @@error code of 2627 (unique key constraint). If it gets it, then it tries to update the row instead.

The reason I ask is that this pattern appears in the one of our stored procedure. Somebody wrote it that way, but it doesn’t appear to work now. Instead, we get an exception thrown back to the java code immediately when the insert call fails.

Do you think this code should work? If so, can you think of a reason why it is not working for us?

New Member
New Member

23 Oct 2008 06:04 AM
No. The code will exit efter the INSERT statement.


1) Do an INSERT with an NOT EXISTS/LEFT JOIN approach
2) Rewrite the constraint to include "IGNORE DUPLICATE KEY".
New Member
New Member

24 Oct 2008 08:29 AM
You may be able to trap the exception the following way:

InnerSproc: TRY...CATCH with the code which throwing the error.

OuterSproc: InnerSproc...CATCH .....

The outer catch is going to work. Not pretty, but works.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS:
New Member
New Member

14 Nov 2008 11:28 AM
You can avoid the whole error handling mess by simply attempting the UPDATE first, then check the number of rows affected with @@ROWCOUNT.

If @@ROWCOUNT from the UPDATE is zero, then do the INSERT.

Acceptable Use Policy