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