Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    6

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

    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.

    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..

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Nov 2011
    Posts
    6

    Thumbs up Got Solved...

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

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    How did you solve it?
    Just curious.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •