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
See Question&Answers more detail:os