sql - MSSQL 中的自动递增字母数字 ID

Question

我有一个生成员工 ID 的现有存储过程。员工 ID 的格式为 EPXXXX，EP 然后是 4 个数值。我希望缩短我的存储过程。

给出上面的表 (tblEmployee)。下面是使用新员工编号插入新员工的存储过程。过程是我必须获取最后一个员工 ID，获取最后 4 位数字(这是数字)，将其转换为整数，加 1 以递增，检查数字是否小于 10、100 或 1000 或等于/大于大于 1000，在向表中插入新记录之前添加前缀。

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

试试这个 -

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