There are many examples of this here in CodeProject so I do encourage you to do some further searching here to get other ideas, however ...
Don't store the formatted EmpCode on your database.
Why? Say Employee C007 moves from being a 'E' type of employee to an 'C' type of employee? You will need to change their ID from "E100" to ... whatever the next number is for 'C'-types. You can't assume that you can use 'C100' for the same employee.
Now put yourself into the real world. A Person, Employee John, is currently employee "E099" but moves jobs into the "C..." world. No problem, except you have had to change his Employee number to "C100" because "C099" is already taken! John is now confused - is his Employee Number 099 or 100? What about old payslips? Payroll has to change too, and any other data that refers to John the person.
Another reason.
Your company is very successful with lots of employees. Finally it takes on Janet as the 1000th employee in the "C" group. You allocate an EmpId to Janet of "C1000". Hm... it doesn't fit, or worse gets stored as "C100" - Now who does "C100" refer to ... Janet or John?
Ok you think, I'll just make EmpIds a letter followed by
four digits. Great, but you have to go update those 999 records you already have on the database and let everybody know - so Janet and John get confused all over again!
I could go on but that's enough.
In summary - If there is formatting to be done, do it in the UI
Here is an example of how you could achieve your automatically generated numbers
create table demo
(
AutoId int identity(1,1),
EmployeeType char(1),
OtherData varchar(max)
)
insert into demo values
('E','some other data1'),
('E','some other data2'),
('C','some other data3'),
('C','some other data4')
which gives
1 E some other data1
2 E some other data2
3 C some other data3
4 C some other data4
but the query
SELECT EmployeeType + RIGHT(REPLICATE('0', 3) + CAST(AutoId as varchar), 3), OtherData
from demo
would produce
E001 some other data1
E002 some other data2
C003 some other data3
C004 some other data4
If you absolutely must have C001 - 999
and E001 - 999 then I suggest you pre-allocate the numbers and just update each candidate number once it has been used. E.g. to generate the numbers
CREATE TABLE EmployeeNumbers
(
id int identity(1,1),
EmployeeType char(1),
EmployeeNo int,
Used int
)
;WITH q AS
(
SELECT 1 AS num
UNION ALL
SELECT num + 1 FROM q WHERE num < 999
)
insert into EmployeeNumbers
SELECT 'E', num, 0 FROM q
UNION
SELECT 'C', num, 0 FROM q option( maxrecursion 999)
Take care if using this approach and allow for more than one user trying to access the table at once.