Add incremental number to username if already exists

[4waystop]

Hi

I've got a table which holds account numbers, usernames and passwords. I already know the account number, but I want to add a username and password. Obviously the username must be unique, and the field is set not to accept duplicates. The username is going to be made out of the user's first initial and surname, plus a number if that username already exists. So we are looking at something like:
jbloggs
jbloggs1
jbloggs2
...etc.
Is there any way I can get SQL to try the insert until it gets a result which works and then return it to me? Through @@IDENTITY maybe?

Also is there any way to auto-generate random strings of characters for passwords with sql, or should I just do this from the asp script?

Thanks
4waystop

 

[rac2]

It might affect how you solve this problem whether it is a one-time-only or an ongoing procedure. I dont think autonumber is the way to go, since that is generally intended to generate arbitrary primary keys for a table.

I would use a stored procedure for this if it is an ongoing procedure. Something like this.

CREATE PROCEDURE createLogin (
   @initial CHAR(1),
   @lastname VARCHAR(20)
   @accountno INT
)
AS

DECLARE @nSimilar INT
DECLARE @vUsername VARCHAR(25)
DECLARE @stemUsername VARCHAR(25)

SET @stemUsername = @initial + @lastname

SELECT @nSimilar = COUNT(*) FROM myTable
WHERE username LIKE @stemUsername + '%'

IF @nSimilar > 0
BEGIN
   @vUsername = @stemUsername + CONVERT(VARCHAR(5),(@nSimilar + 1))
END

UPDATE myTable SET
   username = @vUsername
WHERE account_no = @accountno

The generation of a password with random characters could also be done in the stored procedure using the RAND(seed) function.

DECLARE @pwdDigits FLOAT(24)
SELECT @pwdDigits = RAND(@accountno)

This will give you a floating point value like
1.3925497E-2 which you could convert to VARCHAR(10), 0.0139255, or use to generate a string of letters, or other pattern that you wish.