Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    2

    Unanswered: Generating unique id strings

    Hi
    I want to generate a unique record number in the following format
    Company initials/year/Counter/RecordType (e.g. SDS/04/00123/WB)

    Could someone recommend how I go about generating this number?

    Should I create a separate table with columns for each section of the number and concantenate the columns in ID field of the actual table?

    ... or should I just create a stored procedure to generate the number each time?

    ... or should I do something completely different?

    Regards
    John

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, I'd recommend against doing this unless this code is required by the business process, i.e. recognized by the system users. Such codes are of little or no value to the application.

    If all four of these values already exist in the table, then you could add a calculated column to your table that concatenates them to form the ID. These types of keys are often called "Superkeys", but with the availability of composite keys they are of little use these days and are often difficult to maintain.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2004
    Posts
    2
    Thanks blindman, I'll take your advise and k.i.s.

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    you could create a formula to concatenate the other fields and use it as the default value of a new field in the same row

    I don't think I explained this very well

    er

    read about using formulas as default values in BOL

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    As a default value it would not update automatically if any of it's components change. That's the advantage of a calculated field.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Apologies

    U right of course blindman

    I was'nt thinking

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

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