I agree with Lowell but if you must do it, try this. It's not fully tested but may get you started.

declare

@ID varchar(100),

@FirstName varchar(100),

@LastName varchar(100),

@Counter int,

@RecordCounter int

set @FirstName = 'john'

set @LastName = 'doe'

set @Counter = 0

set @RecordCounter = 0

if exists(select ID from Users where ID = left(@FirstName, 1) + @LastName)

begin

set @Counter += 1

while (@RecordCounter = 0)

begin

set @RecordCounter = (select count(*) from Users where ID = left(@FirstName, 1) + @LastName + '0' + convert(varchar(5), @Counter))

if @RecordCounter = 1

begin

set @ID = left(@FirstName, 1) + @LastName + '0' + convert(varchar(5), @Counter+1)

set @RecordCounter = 1

break

end

else

begin

set @ID = left(@FirstName, 1) + @LastName + '0' + convert(varchar(5), @Counter)

set @RecordCounter = 1

break

end

end

end

else

begin

set @ID = left(@FirstName, 1) + @LastName

end

print 'new ID is ' + convert(varchar(50), @ID)