在 MSSQL 中自动递增字母数字 ID

问题描述：

我有一个生成员工 ID 的现有存储过程.员工 ID 的格式为 EPXXXX、EP 和 4 个数值.我希望我的存储过程被缩短.

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.

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

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

答

试试这个 -

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

在 MSSQL 中自动递增字母数字 ID

相关推荐