Insert and IF Statements

[MarkEmerson]

I want to populate a field with a user Login ID which consists of thier surname and the first letter of thier surname. The problem occurs when I get 2 people with the same initial and Surname if this happens then I want to then use the first 2 letters of thier first name

i.e if there was a john smith and a jerry smith one would be SmithJ and one would be SmithJe

How would I add this condition to the insert statement?

Thanks in advance

 

[swampBoogie]

while @l <= len(@firstName)
begin
  if not exists(select * from t
      where logiId = @lastName + substring(@firstName,1,@l)
      break
   set @l = @l + 1
end
if  @l <= len(@firstName)
insert into t (loginId,<other columns>) values(@lastName + substring(@firstName,1,@l),<other values>)
else
  return -4711 -- no unique id found

This code will also handle the case where SmithJe alraedy exists.

 

[MarkEmerson]

I'll give that a go! Thanks.

I'll let you know how it goes

 

[MarkEmerson]

What would preceed this code?

in other words how does the @firstname become populated?

Thanks

 

[swampBoogie]

I assume you would have this as part of a stored procedure where @firstName and @lastName are sent as parameters.

 

[MarkEmerson]

No I am currently running this from query analyser and will eventually tie it up into a DTS package.

The idea being that it will run everynight and add new users to the database.