Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011

    Unanswered: SQL Server 2000 - Auto numbering format

    Hi members iam using sql server 2000

    i have a table named catecodes
    with the following colums
    and the format of coding is

    is there a code i can use to automatically generate these numbers in this format without typing them manually

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    you mean where the second part of the code begins at 1 again for each different first part?

    no | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Provided Answers: 4
    I hope 0001-0002 is actually stored in two different columns (id_part_1 (0001) and id_part_2 (0002)) and that the combination of both is the primary key. (If not, your database is not normalised and you will keep posting question after question on this forum that require more and more complex solutions.)

    So in your form you will have to be able to select / input/ ... the value of id_part_1, and let the system find the next valid value for id_part_2 once you click the Save button.

    First: get the next valid value for id_part_2.
    SELECT @id_part_2 = COALESCE(max(id_part_2) + 1, 1) 
    from DaTable 
    WHERE id_part_1 = @id_part_1
    Second: use the result in the INSERT statement.
    INSERT INTO DaTable(Id_part_1, Id_part_2, OtherColumn)
    VALUES (@Id_part_1, @Id_part_2, @OtherValue)
    When multiple people can add data at the same time, you will also have to keep en eye out for duplicate primary keys (when two people hit the Save button at the same time, the @id_part_2 of both processes may have the same value. The second one who tries to perform the INSERT will receive an primary key violation Error) and add additional code to cope with those cases.

    As you see this is a lot of work. That is why most people use an Identity column as primary key, and treat all other columns as attributes.
    Last edited by Wim; 04-18-11 at 05:53. Reason: Added "duplicate primary keys"
    With kind regards . . . . . SQL Server 2000/2005/2012

    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

Posting Permissions

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