Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    1

    Unanswered: Unique number of given length

    Please pardon my newbiness, I have a lot of experience in Access, but am just now moving into SQL Server 2005.

    I have created a stored proc that is called from a web page so that our users in the field can request a unique ID for new employees. This is a very simple proc now, simply taking the max of the current values in a table, adding 1, and writing that new value to the table. Here is the proc:

    @First_Name varchar(50),
    @Last_Name varchar(50),
    @GID varchar(50) OUTPUT

    AS
    Select @GID = MAX(DISTINCT GID)+1 FROM GID_STOR;

    INSERT INTO GID_STOR (GID, First_Name, Last_Name, DateTime_Allocated)
    VALUES (@GID, @First_Name, @Last_Name, GetDate())

    The change that I need to make is that I need to start generating an 8 digit number (needs to be stored as text, don't ask!) that does not currently exist in the data table GID_STOR.

    I thought that this would be easier than I'm finding it to be. Can anyone help point me in the right direction?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why don't you use UID, that's why they're made for ?

    Code:
    DECLARE @UID UNIQUEIDENTIFIER
    DECLARE @GID NVARCHAR(50)
    SET @UID = NEWID()
    SET @GID = CAST(@UID AS NVARCHAR(50))
    Have a nice day!

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Use an IDENTITY column.
    IDENTITY (Property)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    GUID is 36 characters, not 8.

    Use the Identity, as Portas suggested, but convert it or display it as an 8 character string by padding it with leading zeros.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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