Do not calculate the NextId - use the built-in functionality provided by sql - i.e. use an
Identity [
^]column
This will auto-increment the id and more importantly will handle multi-user scenarios and race conditions far better than your code finding the previous MAX value.
The value is assigned when you create the row, and will be unique across that table.
If you want to see it in string format just use
select 'FHMS'+ cast (employeeId nvarchar) as thisKey from employeemaster
If for whatever reason (i.e. I can't think of a good enough one), you want to know what the next id is going to be before you insert it then have a look at
@@IDENTITY[
^]