Question programmatically adding a character to ID field

[Arg81]

Afternoon all,

Obviously my table has a Primary Key / ID field. This is currently set as varchar, as the user has to input this (it's not a simple identity integer field) - the field is called WO_No

What I need to know is can the following be done.

When a user enters the WO_No, I need it to check the DB to see if that WO_No already exists. If it does, it will then add a certain character to the end to create a new WO_No

For example:
User enters WO_No 12345 and fills out all other information. When they submit, the program checks, sees WO 12345 exists and submits using WO 12345A
If user enters WO_No 12345 again at a later date, the program checks, sees 12345 and 12345A and saves using 12345B .... etc etc.
Logically speaking, we would never have the need to go beyond 4 or 5 "sub" WO's (they are created when we break work on an existing WO to work on a new WO, then later come back to work on the broken WO)

Questions:
(a) Is this possible, or is it a pain to get the program to assign A,B,C,D,E..etc in that order
(b) Are there any alternatives
(c) Is this in fact not at programming level and is at SQL DB level such as an SP?

Thanks for any advice.

 

[demausdauth]

The main problem I see is:

so you enter 12345 and then go to work on something else and another WO is entered entered as 12345 which is added as 12345A. Things work great so far.

But what happens when a user enters a WO as 123456. So now we have WO numbers as follows:

12345
12345A
123456

If a user comes along and wants to do another 12345 how do we search and differentiate it from the 12345 + Alpha and 12345 + number?

What I would suggest and it might not be feasible, is to add another column to the table

[WO_Seq] smallint default 0 IDENTITY(1,1)

and make it a multiple key table (WO_No, WO_Seq). Now if you insert a duplicate of WO_No, then WO_Seq will increment.

 

[Arg81]

Do you know what, I had the exact same thought last night...

I think I'll add a run_ID column, like you say increment that by 1.

In regards to your 12345 and 123456 question, WO numbers are 5 numbers, so you couldn't get in that situation.
But I still think the run_ID column would be better.

Thanks for the help.