July 31, 2009 at 7:36 am
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
July 31, 2009 at 8:00 am
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
July 31, 2009 at 8:09 am
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
July 31, 2009 at 12:26 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply