error handling for duplicate key value

Certified Associate Developer

Hi All,

I have a requirement  in my project.

Stored Procedure  TEST2 must have error handling for below exception. i have to handle error handling methods to catch the error and return an error status with message to  xyz nightly  refresh  process model.

xyz nightly  refresh must be able to retry calling the stored procedure if the duplicate key exception was caught.

so below is the code which i was trying but it's not working, as i have used insert within try and inserting error details in a separate record_errors table within catch. But while i am debugging SP  through properties

but it's not inserting error details in record_errors table so can anyone help me with any other approach or i am missing anything in it.

 

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Associate Developer

    Hi,

    Can you try adding "ON DUPLICATE KEY" after 22nd line. I think you are getting primary key from the select query(joins) and when you are inserting it is inserting the primary key again(if there are any duplicates also) which is making it duplicate. So, If you add "ON DUPLICATE KEY" it will update when it found duplicate key. Hope this will work.

Reply
  • 0
    Certified Associate Developer

    Hi,

    Can you try adding "ON DUPLICATE KEY" after 22nd line. I think you are getting primary key from the select query(joins) and when you are inserting it is inserting the primary key again(if there are any duplicates also) which is making it duplicate. So, If you add "ON DUPLICATE KEY" it will update when it found duplicate key. Hope this will work.

Children