Inserting same usernames with a integer suffix

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inserting same usernames with a integer suffix

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-04-27 : 13:03:12

Hi
i am inserting a username from the frontend asp.net web form
to table customers which has a field cusername via stred procedure.

What i need is that when i enter a username robert in the front end
the stored procedure should check if the username has already been assigned or not.

if yes it should inserts robert2 else robert3 in that manner.

How can i do that via sp.

Thanks in advance

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-27 : 13:52:41

DECLARE @UsernameCount INT

SELECT @UsernameCount = COUNT(UserId) FROM [user] WHERE username = @username;
SET @UsernameCount = @UsernameCount + 1;
INSERT INTO [user] (username) VALUES (@username + @UsernameCount);

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-04-27 : 14:13:03

DECLARE @UsernameCount INT

SELECT @UsernameCount = COUNT(*) FROM [user] WHERE username = @username;
SET @UsernameCount = @UsernameCount + 1;
INSERT INTO [user] (username) VALUES (@username + CAST(@UsernameCount AS VARCHAR(3)));

The cast around the int value is necessary, otherwise you'll get a conversion error
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'Robert' to data type int.

--
Gail Shaw
SQL Server MVP

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-27 : 14:16:20

The short answer is dont. :)

But, if you must. I have some questions.. Is there one table? Are there two columns you are dealing with (CusomerName and Username)?

The way I read your question is that you have a CustomerName column and you want to use that to auto-create a UserName.. If that is the case then you can use something like:

-- Set up Sample Data
DECLARE @Customers TABLE (ID INT IDENTITY(1,1), CustomerName VARCHAR(50), UserName VARCHAR(50))
INSERT @Customers SELECT 'Robert', 'Robert'

-- Declare variables
DECLARE @CustomerName VARCHAR(50)
DECLARE @CustomerCount INT

-- Populate variables
SET @CustomerName = 'Robert'

SELECT @CustomerCount = COUNT(*)
FROM @Customers
WHERE CustomerName = @CustomerName

-- Perform insert
IF @CustomerCount = 0
BEGIN
	INSERT @Customers
	SELECT @CustomerName, @CustomerName
END
ELSE
BEGIN
	INSERT @Customers
	SELECT @CustomerName, @CustomerName + CAST(@CustomerCount AS VARCHAR(20))
END

NOTE: It's possible, depending on transactional volumn that you could generate two of the same UserNames.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-05-14 : 17:16:43

thanks white fang ,gila monster and lamprey .It worked.

But I have another if the username is more tha 8 characters
it wont accept
hen i have to reduce the firstname by 1 and then compare if the name exists . if yes i have to reduce one more character and so on
like a loop.

How can i achieve this

for example

robertone
if he is there i cant add suffix 2 because it allows only 8 characters
then roberton ---check if exists .
if yes then roberto and again check if the username exists if
yes then add as roberto2.

Hope i am clear .

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-14 : 17:37:04

select left(@username,8-len(convert(varchar(3),@customercount)))
+ convert(varchar(3),@customercount)

Jim

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-05-14 : 17:48:17

sorry jim i did not understand you reply.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-14 : 18:08:34

When you said "it worked", which one worked?

Jim

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-05-15 : 11:31:13

the reply from gila monster worked for me.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-15 : 12:00:19

INSERT INTO [user] (username)
VALUES ( left(@username,8-len(convert(varchar(3),@UsernameCount)))
+ convert(varchar(3),@UsernameCount)
);

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-19 : 01:40:52

You also should wrap this with a transaction, if there are multiple users.

E 12°55'05.63"
N 56°04'39.26"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-05-19 : 10:10:51

quote:
Originally posted by Peso

You also should wrap this with a transaction, if there are multiple users.

And force an exclusive lock on the select. Otherwise you could end up with dupes.

--
Gail Shaw
SQL Server MVP

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-05-19 : 17:50:30

Hi friends and sorry the requirements seem to be keep on increasing.

Now this how i want it ---firstname ,lastname

John Carpenter then username is johnC since the username does not exceed 8 characters . If John C already exists the it is JohnC1
and if johnc1 exists JohnC2....

If user firstname is jennifer lastname: Houston

then username is JennifeH removing r from jennifer but if that already exists then jennifH . It should again check for to see if jennifH exists . If yes the it adds JennifH2...so on.

the main criteria is that the username should be only 8 characters.

Hope i am clear .

thank you for all you replies....

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-05-20 : 03:09:55

Why 8 characters?

--
Gail Shaw
SQL Server MVP

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-05-20 : 10:47:31

Thanks Gila for ur reply...
that has something to do with Active directory integration.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-05-20 : 15:31:39

anything..?

thank you

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-20 : 15:51:24

What have you tried so far?

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-05-21 : 12:01:36


DECLARE @UsernameCount INT
SELECT @UsernameCount = COUNT(*) FROM customer WHERE logon = rtrim(ltrim(@logon))

If @usernamecount = 0
begin 
set @logon = rtrim(ltrim(@logon))  
end 
else 
begin
SET @UsernameCount = @UsernameCount + 1;
set @logon = (rtrim(ltrim(@logon))+ CAST(@UsernameCount AS VARCHAR(3)));
    
while len((rtrim(ltrim(@logon))+ CAST(@UsernameCount AS VARCHAR(3)))) > 8
begin
set @logon = left(@firstname,6)++left(@lastname,1)


DECLARE @UsernameCount1 INT
SELECT @UsernameCount1 = COUNT(*) FROM customerWHERE logon = rtrim(ltrim(@logon))

If @usernamecount1 = 0
begin 
set @logon = rtrim(ltrim(@logon))  
end 
else 
begin
SET @UsernameCount1 = @UsernameCount1 + 1;
set @logon = (rtrim(ltrim(@logon))+ CAST(@UsernameCount AS VARCHAR(3)));
end
   end