Why is it that people keep coming up with requests like this? As a key, this isn't exactly the most efficient. Anyway, to do this, create yourself a sequence table. This table will consist of the two colums, one for the code and the other for the next sequence number. Then, when you need the value update this table and set the sequence value = sequence value + 1 (use an appropriate locking strategy and run this inside a transaction). Next, read the value back again and subtract 1 to get the sequence you need and commit the transaction. This logic works because the update locks the row for the user who is attempting to get the value. If somebody else attempts to update the row when you are inside the transaction, they won't be able to - they have to wait until you have commited or rolled the transaction back. Note - the order you do this in is hugely important; you must do the update before the select for this to work. It's a hack, but it's the cleanest way to do it. I've heard of people attempting to do this type of thing using MAX queries and the like, but these are prone to error.
Deja View - the feeling that you've seen this post before.
My blog | My articles