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
Dev_B
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
BEGIN
UPDATE sometable
END

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?

Thanks
Dev
SwePeso
New Member
New Member

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

Either

1) Do an INSERT with an NOT EXISTS/LEFT JOIN approach
2) Rewrite the constraint to include "IGNORE DUPLICATE KEY".
SQLUSA
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: TRY....call 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: http://www.sqlusa.com/
mimadon
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
---