Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Unanswered: Creating a unique userid and adding digits if it is not.

    I am just returning to the SQL environment so it is taking some time to remember everything so let me apologize in advance for any question that seems to terrible.

    I am creating unique userids by pulling a usernumber, lastname, first initial of the first name and middle initial example 10jonesdw. I am able to create these userids and to eliminate the hyphens and spaces in last names. The issue I have now is that I need each userid to be unique and if it is not to add an ascending number to the name. Examples:
    What I have now:
    10jonesdw, 10jonesdw, 10jonesdw
    What I need to create:
    10jonesdw, 10jonesdw1, 10jonesdw2.

    I do not currently have the check feature. I am using this to create the ids

    insert into _userid
    Select Left(p.usernumber,2) + i.lastname + Left(i.firstname,1) + COALESCE(Left(i.middlename,1),'') As userid
    from dbo.person p,
    dbo.id i
    where p.personid = i.personid;

    This will all be put into a stored procedure once I feel comfortable that I am getting the correct information I need and test there once again.



    I think it should be something similar to this but not sure exactly how to do it in SQL:

    var i INT;
    var newid VARCHAR(64);
    newid = id;
    while(0 < (SELECT COUNT(*) FROM identity WHERE userid = newid)){
    i = i + 1;
    newid = id+i;
    }

    I just do not know how to put this into a function and utilize it on my SQL Server.

    This is the loop I am trying now but it is not working:

    DECLARE @i INT
    DECLARE @id VARCHAR(64)
    DECLARE @newid VARCHAR(64)
    DECLARE @count INT

    SET @id = 'this is the user id'
    SET @newid = @id
    SET @i = 0

    SELECT @count = 0
    WHILE 1 = 1
    BEGIN
    SELECT @count = (SELECT COUNT(*) FROM usertest WHERE userid = @newid)
    IF @count = 0
    EXEC('INSERT INTO usertest (userid) VALUES (' + @newid +')')
    BREAK
    ELSE
    SET @i = @i + 1;
    SET @newid = @id+@i;
    CONTINUE
    END


    Thank you in advance for the help,

    Pete
    Last edited by phk; 07-31-09 at 15:28.

  2. #2
    Join Date
    Apr 2007
    Posts
    183
    Something like this?
    Code:
    SELECT TOP 1	'10jonesdw' + CAST(Number AS VARCHAR(12))
    FROM		master..spt_values AS v ON v.Type = 'P'
    LEFT JOIN	UserTable AS ut ON SUBSTRING(ut.UserName, 1, 9) = '10jonesdw'
    WHERE		ut.UserName IS NULL
    ORDER BY	v.Number
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Won't that incorrectly increment if the usernames are 10jonesdw, 10jonesdwabc, 10jonesdw?
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How's this? *shrug*
    Code:
    --This is to mimic your destination table
    CREATE TABLE #users (
       name   varchar(50)
     , number int
     ,   CONSTRAINT pk_users PRIMARY KEY (name, number)
    )
    
    --Initial sample population from my db
    INSERT INTO #users (name, number)
    SELECT surname + Left(forename, 1) As username
         , Row_Number() OVER (PARTITION BY surname + Left(forename, 1) ORDER BY NewID()) As number
    FROM   people
    WHERE  surname = 'smith'
    AND    Left(forename, 1) IN ('A', 'B')
    
    --Show initial output
    SELECT name + Convert(varchar(11), number) As username
    FROM   #users
    ORDER
        BY username
    
    --Test multiple inserts
    ; WITH new_users AS (
      SELECT 'SmithA' As name
      UNION ALL SELECT 'SmithA'
      UNION ALL SELECT 'SmithA'
      UNION ALL SELECT 'SmithA'
      UNION ALL SELECT 'SmithB'
      UNION ALL SELECT 'SmithC'
      UNION ALL SELECT 'SmithD'
      UNION ALL SELECT 'SmithD'
    )
    INSERT INTO #users (name, number)
    SELECT x.name
         , Coalesce(u.max_number, 0) + Row_Number() OVER (PARTITION BY x.name ORDER BY NewID()) As number
    FROM   new_users As x
     LEFT
      JOIN (
            SELECT name
                 , Max(number) As max_number
            FROM   #users
            GROUP
                BY name
           ) As u
        ON u.name = x.name
    
    --Show the result
    SELECT name + Convert(varchar(11), number) As username
    FROM   #users
    ORDER
        BY username
    
    --Tidy up
    DROP TABLE #users
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2009
    Posts
    3

    Thank you

    Thanks everyone for the help.

    I ended up using something similar to this:

    -- create table variable for testing
    DECLARE @usertest TABLE(userid VARCHAR(50))
    DECLARE @id VARCHAR(50)
    DECLARE @newid VARCHAR(50)
    DECLARE @count INT
    -- test data
    INSERT INTO @usertest
    (
    userid
    )
    SELECT
    'user'
    UNION ALL
    SELECT
    'user1'
    UNION ALL
    SELECT
    'user2'
    -- return existing test data
    SELECT * FROM @usertest

    SET @id = 'user'
    SET @count = 1
    SET @newid = @id
    -- get next id
    WHILE EXISTS (SELECT 1 FROM @usertest WHERE userid = @newid)
    BEGIN
    SET @newid = @id + CONVERT(VARCHAR(40), @count)

    SET @count = @count + 1
    END
    -- got newest id insert it
    INSERT INTO @usertest
    (
    userid
    )
    VALUES
    (
    @newid
    )

    SELECT * FROM @usertest

    but am testing to see if I like what gvee did was better for the procedure I am going to be running.

    Thanks again.

    PHK

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I can't think of any reason why you want to make it so hard on yourself. If you want to generate a unique userid, why not use a unique sequential number?

    What if someone made a mistake while entering a person's last name, first name or middle initial ? Will you correct your unique userid too?
    If you do, you're in to a lot of trouble, think about updating all the FK's that reference this PK, think about the FK constraints preventing you from doing so.
    If not, then you are not enforcing any relation between the userid and the values of the lastname, first name and middle initial, so you could have used a sequential number from the start.

    The very first database application I ever wrote was a redesign of a system that was written in dBase IV by a few end users. The user-base insisted that I kept the old PK consisting of gender, part of the first and part of the last name + sequence, as all their reports were based on that key and the logic behind it. They required me to make it possible to automatically change the PK (and FK's referencing it) whenever a correction about gender, or name would happen. They were power users and were planning at that time to write their own reports against the database, so I could not use a sequence Id as they would find out. It being my first job and assignment I didn't resist their &#167;@#%$ requirement (though I tried for a long time). The consequences of that requirement ... [horrible flash-backs go here]...

    One good advice, don't do it. You will sleep a lot better.
    Last edited by Wim; 08-03-09 at 12:14.
    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

  7. #7
    Join Date
    Jul 2009
    Posts
    3

    Thank you Wim

    Thank you, I am trying to fight it a bit, but am new to the district and they want to implement gaggle into the school district by using userids and passwords that will be associated with the database we currently have. We are using active directory to create the userids which are the grad year, last name, first init, and middle init. They want me to create a procedure that creates that in our student database and then stop using active directory. It is a very small tech department for a very large district and right now I am the only one with any SQL experience at all. I will see if I can get them to reconsider especially after your post. Thanks for the help it will come in handy when I go to the administration about this issue.

    Pete

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Ok, now I know the reason behind this requirement. If I guess correctly, you already have a Student table with some PK, and now you have to add an extra userdId that consists of "usernumber, lastname, first initial of the first name and middle initial". If that is the case, I see no problems.

    My objection goes against using a "usernumber, lastname, first initial of the first name and middle initial"-column as PK. That is asking for troubles.
    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

Posting Permissions

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