Generating Incremental Values preceded with letters
-
Hi, am using sql 2005 as backend Am trying to generate auto-incremental values that is preceded with letters from my C# codes, based on user text input into database. The user input could be MAN, LADY, UNCLE etc. However, I intend to generate values into the database such as: MAN00001 MAN00002 LADY0001 LADY0002 UNCLE0001 etc. Kindly show me the way
-
Hi, am using sql 2005 as backend Am trying to generate auto-incremental values that is preceded with letters from my C# codes, based on user text input into database. The user input could be MAN, LADY, UNCLE etc. However, I intend to generate values into the database such as: MAN00001 MAN00002 LADY0001 LADY0002 UNCLE0001 etc. Kindly show me the way
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.