Skip to main content
madhup0003
madhup0003
March 5, 2024
Question

error handling for duplicate key value

  • March 5, 2024
  • 5 replies
  • 0 views

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.

 

    5 replies

    tejak2639
    tejak2639
    March 6, 2024

    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.

    tejak2639
    tejak2639
    March 6, 2024

    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.

    madhup0003
    madhup0003Author
    March 6, 2024

    Hi Tejak, Thank you for your response, I have update my actual code on post can you check and let me know where i have to use "ON DUPLICATE KEY" 

    because it's showing me syntax error near ON , which you have earlier mention, please go through the actual code and let me know.

    tejak2639
    tejak2639
    March 6, 2024

    LEFT JOIN Project
    ON ActivityTechnologyBudget.ProjectID = Project.IDProject
    ON DUPLICATE KEY
    After line 388. don't play comma before "ON".
    If this doesn't work,that should be sync with insert query. write that at the end of insert query and check.

    Terms & ConditionsAccessibility statement