1 Without DDL for all referenced tables, it's not possible to evaluate this sproc more than superficially (see $7 below)
2 To debug the sproc, you'll need to add in code stubs that track the values that are being duplicated
3 You'll also need to similarly debug the called sproc for which code is not posted
4 The logic looks incomplete, eg (i) error trapping and reporting is inadequate (ii) several contingencies can be imagined causing a SELECT or UPDATE to fail, but the code covers no such possibilities
5 Absolutely this logic needs to be encapsulated in a transactional block with rollback and retries to handle deadlocks
6 Also all entity and foreign key constraints need to be tracked and errors related to them need to be reported out eg using SIGNAL
7 Atomicity is one of Codd's 12 basic rules of relational databases, if you're unfamiliar with it, please read a primer ag
https://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf
Edited 3 time(s). Last edit at 07/15/2022 09:23PM by Peter Brawley.