How to generate unique incremental employee code(pattern: E0001, E0002, E0003, ... ) in the INSERT stored procedure in SQL Server?

Question

I want to generate unique employee code at a time of inserting a single record in INSERT stored procedure.

First-time procedure call will generate employee code as E0001, the second call will generate E0002, ...

The expected output should look like,

EmployeeCode    EmployeeName
-------------------------------
E0001           A
E0002           B
E0003           C
E0004           D
E0005           E
  '             '
  '             '
  '             '
E9999           ZZZ

Answer 1

You can embed a sequence value into a custom-formatted string in a default, and enforce it with a check constraint. Like this:

--drop table if exists Employee
--drop sequence seq_Employee
--go

create sequence seq_Employee
start with 1
increment by 1    
go

CREATE TABLE Employee
(
    EmployeeCode char(5) PRIMARY KEY 
        default 'E' + format((next value for seq_Employee),'0000' )
        check (EmployeeCode like 'E[0-9][0-9][0-9][0-9]'),
    EmployeeName VARCHAR(50) NOT NULL
)    
go

insert into Employee(EmployeeName)
values ('A'),('B'),('C'),('D'),('E')

select *
from Employee

Outputs

EmployeeCode EmployeeName
------------ --------------------------------------------------
E0001        A
E0002        B
E0003        C
E0004        D
E0005        E