How do I make a stored proc ingore an error?

Question

How do I make a stored proc ingore an error?

tomiz

SSCommitted

Points: 1842
More actions
July 5, 2003 at 1:55 pm

#80517
Hi Gurus!
How do I make a stored procedure totally ignore a specific error? I've tried using @@ERROR, BEGIN TRAN and ROLLBACK but it doesn't seem to work at all.
This is what I've came up with so far:
```
BEGIN TRAN

INSERT testtable (myPK)

VALUES ('duplicate')



IF @@ERROR <>  0

ROLLBACK

ELSE

COMMIT
```
Now, why does the code still throw an error #2627?
Thanks for any help!
/Tomi

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

mccork SSC-Insane Points: 22288 More actions · Answer 1

SQL Server is checking compliance with constraints before your code checks @@error. It will not allow duplicates in the primary key and so throws the error regardless of how you attempt it.

Why don't you check for existence of the key before any attempt to insert?

Cheers,
- Mark

tomiz SSCommitted Points: 1842 More actions · Answer 2

Thanks McCork!

quote:
Why don't you check for existence of the key before any attempt to insert?

Yes, I could do that but someone told me that it can be done with error checking... and it would be faster to do that instead of scanning the PK twice.

All I want it to do is not to send an error to ADO if the insert fails. I don't want a duplicate key inserted.

Thanks again!

/Tomi