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 

Solution 1

Try this one -

CREATE PROCEDURE dbo.NewEmployee

     @EmployeeName VARCHAR(50)

AS BEGIN

SET NOCOUNT ON;

     INSERT INTO dbo.tblEmployee(Employee_ID, Name)
     SELECT 
            'EP' + RIGHT('0000' + CAST(Employee_ID + 1 AS VARCHAR(4)), 4)
          , @EmployeeName
     FROM (
          SELECT TOP 1 Employee_ID = CAST(RIGHT(Employee_ID, 4) AS INT)
          FROM dbo.tblEmployee
          ORDER BY Employee_ID DESC
     ) t

END 

Solution 2

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))))

Solution 3

I don't think you need a Stored Procedure , Try using Ranking Functions

select 
'EP'+RIGHT('000000'+ CAST(ROW_NUMBER() OVER (ORDER BY Name) AS VARCHAR(6)), 4) 
    AS [emp_code]
,
Name
FROM emp1 WITH(NOLOCK)

SQL Fiddle

EDIT

select 
'EP'+RIGHT('000000'+ CAST((ROW_NUMBER() OVER (ORDER BY Name)+10) AS VARCHAR(6)), 4) 
    AS [emp_code]                                        --^Add the last Emp no.
,
Name
FROM emp1 WITH(NOLOCK)

SQL Fiddle

Comments

[Damien_The_Unbeliever]

It would be far easier to store the numeric portion in a column by itself (possibly an IDENTITY column) and then have a computed column that produces this formatted value. In such a scenario, whether you also have a column to store the prefix or just hard code it to EP is a design decision.

[nigel]

it's working, but what the process is there are existing records in the table and I need to put sequential ID for the employee and not by the name.

[nigel]

The table is already existing and i don't have the privilege to modify the structure. still thanks for the reply, this might be useful for future use.

[Nirmal Subedi]

You are creating an Whole Procedure That would produce an ID for an Just a SINGLE Transaction!!! Is this sounds good? Why don't you have an Procedure that would Generate an ID for All the Transactions that would have in your Application? If so then, now, You can suggest your Senior's there that way. Dont you! I think you have to. The way, you are doing, If you have 200 Types of Transactions like Employee, Like Agent, Supplier, Customer...What, Will you create Single Procedure foreach to generate an id? It would better you have an single procedure which could manage alllll. Suggest your Senior.

[nigel]

It's generating an error: "Conversion failed when converting the varchar value 'EP0004' to data type int."

[Prahalad Gaggar]

What do you mean by it's arranging the name and not the employee ids I am not getting you. If you can provide me the Complete data , than i can generate the full proof script.

[nigel]

in my sample table the arrangement were EP0001 John, EP0002 Peter, EP0003 David, EP0004 George. Using the SQL Fiddle the output is. EMP011 David EMP012 Geroge EMP013 John EMP014 Peter

[Prahalad Gaggar]

@nigel Edited the answer.