sql - Auto increment Alphanumeric ID in MSSQL

Question

I have an existing Stored procedure that generate employee ID. The employee ID have a format of EPXXXX, EP then 4 numeric values. I want my stored procedure to be shorten.

given the table (tblEmployee) above. Below is the stored procedure for inserting the new employee with the new employee number. The process is I have to get the last employee id, get the last 4 digits (which is the number), convert it to integer, add 1 to increment, check if the number is less than 10, 100 or 1000 or equal/greater than 1000, add the prefix before inserting the new records to the table.

create procedure NewEmployee

@EmployeeName VARCHAR(50)

AS
BEGIN

    SET NOCOUNT ON

    DECLARE @lastEmpID as VARCHAR(6)
    SET @lastEmpID =
    (
        SELECT TOP 1 Employee_ID
        FROM tblEmployee
        ORDER BY Employee_ID DESC
    )

    DECLARE @empID as VARCHAR(4)
    SET @empID = 
    (
        SELECT RIGHT(@lastEmpID, 4)
    )

    DECLARE @numEmpID as INT
    @numEmpID =
    (
        SELECT CONVERT(INT, @empID) + 1
    )

    DECLARE @NewEmployeeID as VARCHAR(6)
    IF @numEmp < 10
        SET @NewEmployee = SELECT 'EP000' + CONVERT(@EmpID)
    IF @numEmp < 100
        SET @NewEmployee = SELECT 'EP00' + CONVERT(@EmpID)
    IF @numEmp < 1000
        SET @NewEmployee = SELECT 'EP0' + CONVERT(@EmpID)
    IF @numEmp >= 1000
        SET @NewEmployee = SELECT 'EP' + CONVERT(@EmpID)

    INSERT INTO tblEmployee(Employee_ID, Name)
    VALUES (@NewEmployeeID, @EmployeeName)

END 

Answer 1

I'm not suggesting over what you have currently but, i'd do this way. This is the way I've implemented in my application. Which im gonna give you. Hope you Like this. This is fully Dynamic and Works for all the Transaction you could have.

I've a table Which hold the Document Number as :

CREATE TABLE INV_DOC_FORMAT(
    DOC_CODE VARCHAR(10),
    DOC_NAME VARCHAR(100),
    PREFIX VARCHAR(10),
    SUFFIX VARCHAR(10),
    [LENGTH] INT,
    [CURRENT] INT
)

Which would hold the Data Like :

INSERT INTO INV_DOC_FORMAT(DOC_CODE,DOC_NAME,PREFIX,SUFFIX,[LENGTH],[CURRENT])
VALUES('01','INV_UNIT','U','',5,0)

INSERT INTO INV_DOC_FORMAT(DOC_CODE,DOC_NAME,PREFIX,SUFFIX,[LENGTH],[CURRENT])
VALUES('02','INV_UNIT_GROUP','UG','',5,0)

And, i'd have a fUNCTION OR Procedure but, i've an function here Which would generate the Document Number.

CREATE FUNCTION GET_DOC_FORMAT(@DOC_CODE VARCHAR(100))RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @PRE VARCHAR(10)
    DECLARE @SUF VARCHAR(10)
    DECLARE @LENTH INT
    DECLARE @CURRENT INT
    DECLARE @FORMAT VARCHAR(100)
    DECLARE @REPEAT VARCHAR(10)
    IF NOT EXISTS(SELECT DOC_CODE FROM INV_DOC_FORMAT WHERE DOC_CODE=@DOC_CODE)
        RETURN ''

    SELECT @PRE= PREFIX FROM INV_DOC_FORMAT WHERE DOC_CODE=@DOC_CODE
    SELECT @SUF= SUFFIX FROM INV_DOC_FORMAT WHERE DOC_CODE=@DOC_CODE
    SELECT @LENTH= [LENGTH] FROM INV_DOC_FORMAT WHERE DOC_CODE=@DOC_CODE
    SELECT @CURRENT= [CURRENT] FROM INV_DOC_FORMAT WHERE DOC_CODE=@DOC_CODE
    SET @REPEAT=REPLICATE('0',(@LENTH-LEN(CONVERT(VARCHAR, @CURRENT))))
    SET @FORMAT=@PRE + @REPEAT +CONVERT(VARCHAR, @CURRENT+1) + @SUF
    RETURN @FORMAT
END

You can use the Function like :

INSERT INTO     INV_UNIT(UNIT_CODE,UNIT_NAME,UNIT_ALIAS,APPROVED,APPROVED_USER_ID,APPROVED_DATE) 
            VALUES(DBO.GET_DOC_FORMAT('01'),@Unit_Name,@Unit_Alias,@APPROVED,@APPROVED_USER_ID,@APPROVED_DATE)

--After Transaction Successfully complete, You can

UPDATE INV_DOC_FORMAT SET [CURRENT]=[CURRENT]+1 WHERE DOC_CODE='01'

Or, you can create an Single Procedure which would handle all the things alone too.

Hope you got the way...

Hence, Looking at your Way, you are making an Mistake. You are getting SET @lastEmpID = ( SELECT TOP 1 Employee_ID FROM tblEmployee ORDER BY Employee_ID DESC )

Last employee id, and then you are manipulating the rest of the ID. This would create or reuse the ID that was generated earlier however deleted now. Suppose EMP0010 was there. After some day that EMP has been Deleted. So, When you again create an Employeee next time, You gonna have Same Emp ID you had before for anohter Employe but no more exits however. I dont think thats a good idea.

And, Instead of this :

DECLARE @NewEmployeeID as VARCHAR(6)
    IF @numEmp < 10
        SET @NewEmployee = SELECT 'EP000' + CONVERT(@EmpID)
    IF @numEmp < 100
        SET @NewEmployee = SELECT 'EP00' + CONVERT(@EmpID)
    IF @numEmp < 1000
        SET @NewEmployee = SELECT 'EP0' + CONVERT(@EmpID)
    IF @numEmp >= 1000
        SET @NewEmployee = SELECT 'EP' + CONVERT(@EmpID)

Which you used to repeat an Zero. You would use Replicate Function() of SQL. Like above on the Example of Mine.

SET @REPEAT=REPLICATE('0',(@LENTH-LEN(CONVERT(VARCHAR, @CURRENT))))