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.

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.

Answers


(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


Need Your Help

What is the need of _diffs folder while theme design in Liferay?

java eclipse liferay liferay-ide liferay-theme

I know I can put some files into _diffs folder and these files will be placed up upon deploy. But I can also edit files directly inside docroot and they will work too. Under Eclipse it is even more

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.