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

when i click notification open another my application

java android

when i'm in my application,i click my application's notification open another my application,there are two same application on screen and btw i'm create notification from intent service

Table creation with php and mysqli

php mysql login

i'm trying to do a table with php which takes the data from my database and build's the table im html but despite my while loop , only the 1st row is getting into the table. If i change the while l...

Downloading a web page and searching a text with python

python search text web

I'm trying to scrape specific text from a website. Because I'm new in Python, I find it difficult to scrape a text with a single script, so I used this code first: