Need Help on Store Procedure to generate Custom ID for each Asset

tonytonse · #1 (**permalink**) 01-07-12, 05:20

Dear All,
I need a help on creating a store procedure which generates custom IDs for each asset. I am programming for Fixed Assets in VB6 with SQL Server 2005. For example, when a new Asset is added ,I need to auto generate the ID based on existing IDs. New ID should not exist in tblAssets table.

Quote:

Table Name : tblAssets
Fields : AssetID > Int,Primary Key,this is internal ID (identity seed)
AssetExtID >nvarchar(50),this is external ID, need to generate/user entered.
Below is the example of data in tblAssets :

AssetID AssetExtID ProjectID ItemName Qty UOM UnitCost .....
1 PROSP-00001 PROSPERITY SPLIT-AC 2 NOS $200
2 PROSP-00002 PROSPERITY LAPTOP 1 NOS $500
3 UNIII-00001 UNION III LAPTOP 5 NOS $400
4 UNIII-00002 UNION III RECEIVER 2 NOS $312

The AssetExtID depends on the ProjectID which is in tblProjects.
I will take care of the first 5 characters to generate. But the number part I need to generate by checking existing data. The AssetExtID should not be duplicate. Its unique for each asset. Please help on this. Your help is always appreciated.

Thank You..

Brett Kaiser · #2 (**permalink**) 01-09-12, 16:22

I have a solution (the identity column) but I need another one...more difficult, and has the possibility of duplicates...what should I do?

Drop back and Punt

Making "Smart" Values derived from data is Always a bad idea.

Your Item name is a good enough "ID" for me

Wim · #3 (**permalink**) 01-09-12, 18:53

You already have an excellent Id, AssetID. There is technically no reason for another "Id".

A) Is there any reason why you want to make your life harder than it should be?
B) Are there others (aka "users", "management", "my boss", ...) who want to make your life harder than it should be?

If the answer to A) is No: don't create another Id.
If the answer to A) is Yes: don't create another Id. Invent something else to make your life miserable.
If the answer to B) is No: don't create another Id.
If the answer to B) is Yes: Hmm, you could create a table that stores per ProjectID (eg. 'PROSPERITY') the first part of the associated AssetExtID (eg 'PROSP') and the highest number that you have already assigned (eg 2).

Like:

Code:

Table TableToAssistMeInmakingMyLifeHarder
ProjectID  firstPart  maxSeqNr
PROSPERITY PROSP      2
UNION III  UNIII      2

Let your sp locate the record in TableToAssistMeInmakingMyLifeHarder that corresponds to the ProjectID. Get the value of maxSeqNr, increment it by 1, UPDATE that new value into TableToAssistMeInmakingMyLifeHarder and use the incremented value to generate the extra Id.
When your sp can't find a record for the given ProjectID,
1) INSERT a new record into TableToAssistMeInmakingMyLifeHarder with maxSeqNr = 1 and firstPart = LEFT(ProjectID, 5).
2) But perhaps you don't want that to happen, because "UNIII" indicates that the first part of your extra Id are not just the first 5 characters of ProjectID, but something you have to input manually. You may want to raise an error and end your transaction in this case.
A solution to this, is to extend the Project table with an extra NOT NULL column for the first part of the id.
Also make sure that your code is resilient towards two INSERTS that almost happen simultaneously, so the same value of maxSeqNr is not used more than once.
See how much fun all of this is? And knowing none of this is needed in any way ...

We can only hope that there are no others who want to make your life harder than it should be.
But if there are, be strong.

tonytonse · #4 (**permalink**) 01-10-12, 02:43

Dear All,
I got the problem solved. Thank you all..
Byee.

Wim · #5 (**permalink**) 01-10-12, 10:22

How did you solve it?
Just curious.

Internet Services

Web Development

Digital Marketing

Consumer Tech