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

i'm new sql server stored procedures. wondering how go writing stored procedure. scenario is:

the procedure takes firstname , lastname parameters, on basis of takes first letter or firstname , first letter of lastname , appends number end of , returns string. if string exists in database increment number , return 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 case output ad002 instead of 001 since value ad001 exists in database created example 1)

any problem appreciated. thanks.

(update)
can use logic in procedure :

declare    @firstname varchar(10)= 'aaa',        @lastname varchar(10)=  'ddd',    @output varchar(20)  insert name_table     select        @firstname,        @lastname,       substring(@firstname,1,1)+       substring(@lastname,1,1)       +right('000'+cast(coalesce(max(substring(output,3,4)),0)+1 varchar) ,4)           name_table            output substring(@firstname, 1, 1) + substring(@lastname, 1, 1) + '%'   select * name_table 

sql fiddle