Post reply

Procedure to create string ID

  • February 28, 2004 at 11:43 pm

    #84306

    Dear nice guys,

    How to create procedure or trigger in SQL Server 2000 to create string ID when new records inserted.

    Could anyone give some example for it??

     

    Thanks a lot!!!!

     

  • February 29, 2004 at 6:20 am

    #496363

    I guess the first question is why a string id? Typically integers or uniqueidentifiers are used. Unless the value will be made of some component of the data itself even a string key will usually be derived from a number somewhere.

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • February 29, 2004 at 2:10 pm

    #496371

    Andy,

    The reason for using string ID is that I need to use a character in the beginning of ID e.g. A00001. The first letter is to indicate project code and the remaining number is to identify student.

    How can I do??

  • February 29, 2004 at 10:19 pm

    #496378

    You can create a USER-DEFINED FUNCTION (UDF) to generate a complex ID, and then use that UDF on an AFTER INSERT TRIGGER for the table.If you don't need a complex ID, just simply combine any string that you need on AFTER INSERT TRIGGER for the table.Cheers -- Hendra

  • February 29, 2004 at 10:36 pm

    #496382

    Hi nghoiming02
     
    I provide a user viewable serial number on a table in addition to its primary key value.  It is similar to your requirement.
     
    I do this by fetching the current top numeric value (bypassing the text prefix, in my case this is XA or XB but could be anything.  I want the serial number to be 4 digits or more so from the top number (incremented by 1)  i calculate the number of digits and ensure it is at least 4.  I then form the new serial number using STR and pad with leading zeros.  Finally I add the prefix and update the new serial number on the new record, already added to the table.
     
    I'm sure there is a faster, more succinct way to do this but I only have a maximum of about 20 of these per day, so this works fine.
     
     
    Here is my way:
     
    -- Generate a new name using the top number in the table

    SELECT @shNum = MAX(CAST(ISNULL(RIGHT(strName, (LEN(strName) - 2)), 0) AS int)) + 1

    FROM tblName

     
    -- Check for no entries in the table

    if @shNum is null

        select @shNum = 1

     
    select @shDigits = ceiling(log10(@shNum + 1))
     
    -- Min 4 digits

    if @shDigits < 4

        set @shDigits = 4

     
    set @strName = REPLACE(STR(@shNum, @shDigits, 0), ' ', '0')

    -- Form the Name

    if @bFlag = 0

        set @strName = 'XB' + @strName

    else

        set @strName = 'XA' + @strName

     
    -- Write the new batch name to the new batch record

    UPDATE tblName SET strName = @strName

    WHERE lngIdName = @lngId

  • February 29, 2004 at 11:23 pm

    #496385

    If your table will have seprate columns for ProjectCode and Student and your application design permits, You may even use computed Columns !!

    create table test

    ( projectcode char(1),

      studentnum int ,

      uniquecode as projectcode +  right ( '00000' + convert(varchar,studentnum ) , 5) char(6)

    )

     


    -- Amit


    "There is no 'patch' for stupidity."


    Download the Updated SQL Server 2005 Books Online.

  • March 1, 2004 at 12:53 am

    #496394

    I would also use two separate columns for project code and student (ID ?). The rest (eg, the presentation of A00001 can easily be done at the client application.

     

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 1, 2004 at 2:19 am

    #496406

    Yes good point Frank,

    I only use this serial number technique where the prefix only tells the user something about the 'type' of record, not inforamtion contained in other fields or tables.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply