Post reply

How do I make a stored proc ingore an error?

  • 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

  • July 5, 2003 at 3:46 pm

    #464220

    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

  • July 6, 2003 at 2:25 am

    #464224

    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

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

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