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

• pkinnaman

  SSC Enthusiast

  Points: 109

  More actions

  July 31, 2009 at 7:36 am

  #99816

  Hello 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.

  Thank you in advance for the help.

  Pete

• MartinSchmidt

  Mr or Mrs. 500

  Points: 525

  More actions

  July 31, 2009 at 8:00 am

  #1032943

  How do you create these now ?

  What i would do was to add at check to your code, that checks if the id exists, if it does add a number to the end of it and run the check again.

  When you come up with an id that does not exist, you'll just save that in the database

  /Msc

• pkinnaman

  SSC Enthusiast

  Points: 109

  More actions

  July 31, 2009 at 8:09 am

  #1032951

  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 am looking to do something like this but 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;

  }

  not sure if that helps but hope it does.

  Thank you,

  Pete

• pkinnaman

  SSC Enthusiast

  Points: 109

  More actions

  July 31, 2009 at 12:26 pm

  #1033109

  Here is what I am currently trying but I am having issues with it. First time trying a loop in SQL:

  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