Generate a auto sequential number at the time of entry

Create table tbl_user(
UserId varchar(10)
)
 
insert into tbl_User values ('M001')
insert into tbl_User values ('M002')
insert into tbl_User values ('M043') 

select * from tbl_User

Check my below stored proc

Create proc asp_GetAutoincrementedId
Begin
Declare @MaxId int
select @MaxId=MAX(SUBSTRING(Userid,2,LEN(UseriD)-1))+1 from tbl_user
Select  Case when @MaxId<10 then 'M00'+ CAST(@MaxId as varchar(5)) 
when @MaxId<100  then 'M0'+ CAST(@MaxId as varchar(5)) 
when @MaxId<1000  then 'M'+ CAST(@MaxId as varchar(5)) END

END