Show me your ID

One of the joys of working on software that has been outsourced to third parties, is fixing all the third party issues that only get discover after the support contract has expired.

Apart from having massive portions of duplicated code all over a payment system, I had to discover why some payments were going missing, and some were being assigned to the wrong businesses. After days of refactoring the code to remove all the duplication, I found this little nugget in one of the stored procedures...

insert into tbl_invoiceitems (invoice_id, list_id,voucher_id)

values (IDENT_CURRENT('tbl_invoice'), IDENT_CURRENT('tbl_directoryListing'), @vid)

Confused with is strange query, I decided to research this unusual Ident_Current. Basically it allows you to get the last identity insert into a table. That's fine if the stored procedure you are using has done that previous insert, but these stored procedures were called one after the other hoping to god that no other inserts were done between each other. They even returned the new inserted identities, but promptly forgot about them so they could hope that the identity was waiting in the right place in a queue in sql server. It's kind of like going to an airport baggae retrivval system, and grabbing the first case of the same colour. 9 times out of 10, it'll probably be yours and you can carry on your day. Occasionally however - someone runs off with the wrong case and everything goes tits up.

I even saw an attempt at rectifying the problem, where the developer had created the exact same procedure, adding a TBL_DircetoryListing identity property, but risking the invoice number column.

I recitified it by removing the IDENT_CURRENT clauses. Now I have to test the 30 combinations of the code repetition throughout the awful code (with slight changes that i'm almost too scared to fix)