SQL Server Stored Procedure to generate a Code based on firstname and lastname

Question

I'm new to SQL Server stored procedures. I was wondering how to go about writing this stored procedure. The scenario is:

The procedure takes firstname and lastname as parameters, on basis of that it takes the first letter or firstname and first letter of lastname and appends a number to the end of it and returns this string. If the string already exists in the database it will increment the number and return the string.

Example 1:

firstname: abc    
lastname: def    
output: ad001

Example 2:

firstname: pqr    
lastname: mno    
output: pm001

Example 3:

firstname: aaa    
lastname: ddd
output: ad002 

(in this case output is ad002 instead of 001 since the value ad001 already exists in the database which was created by example 1)

Any help with this problem would be appreciated. Thanks.

Answer 1

(Update)
You can use this logic in your procedure :

Declare
   @firstname varchar(10)= 'aaa',    
   @lastname varchar(10)=  'ddd',
   @output varchar(20)

insert into name_table 
   select 
      @firstname, 
      @lastname,
      substring(@firstname,1,1)+
      substring(@lastname,1,1)
      +RIGHT('000'+cast(COALESCE(max(substring(output,3,4)),0)+1 as varchar) ,4)
   from 
      name_table 
   where 
      output like substring(@firstname, 1, 1) + substring(@lastname, 1, 1) + '%'


select * from name_table

SQL Fiddle