SQL Server Stored Procedure to generate a Code based on firstname and lastname
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.
firstname: abc lastname: def output: ad001
firstname: pqr lastname: mno output: pm001
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.
(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