store procedure takes the tablename, columnname as input parameter and retruns the next number
Employee table will return output as E11090001
create proc GetNextNumber
@tableName nvarchar(100),
@columnName nvarchar(100)
as
declare @strSql nvarchar(1000)
set @strSql = ' select ''' + upper(right(@tableName,1)) + ''' +
left(convert(nvarchar,GETDATE(),12),4) +
right(''0000'' + convert(nvarchar,max(right(' +@columnName + ',4))+ 1),4)
from ' + @tableName
exec sp_executesql @strsql