-
Auto Generated Auto Incremented Alphanumeric Sequential Code in SQL SERVER
over 8 years ago
over 8 years ago
Create a table tbEmployeeMaster by following query :-
Create a user defined function 'fnEmpCode' to get new Employee code of specified length and specified prefix using following query :-
CREATE FUNCTION fnEmpCode(@EmpPrefix VARCHAR(10),@EmpLength INT)RETURNS VARCHAR(20)ASBEGIN--Get maximum Emp id from table i.e. the last generated Empid in the table. (initially 0 if table has no data).DECLARE @MaxEmpId INT;SET @MaxEmpId= ISNULL((SELECT MAX(EmpId) FROM tbEmployeeMaster),0)--Increment maxempid by 1 to get next emp id.SET @MaxEmpId+=1DECLARE @EmpCode VARCHAR(20),@i INT=1;WHILE(@i=1)BEGIN--Generate new emp code of specified code length prefixed by specified prefix passed as parameters.SET @EmpCode=@EmpPrefix +RIGHT(REPLICATE('0', @EmpLength-1) + CONVERT(VARCHAR(20),@MaxEmpId),@EmpLength)--Check generated emp code. If already exists then get next emp code untill we get fresh emp code.IF EXISTS(SELECT 1 FROM tbEmployeeMaster WHERE EmpCode=@EmpCode)BEGINSET @MaxEmpId +=1ENDELSEBEGINSET @i=0ENDEND--Return newly generated emp codeRETURN @EmpCodeEND
CREATE FUNCTION fnEmpCode
(
@EmpPrefix VARCHAR(10),
@EmpLength INT
)
RETURNS VARCHAR(20)
AS
BEGIN
--Get maximum Emp id from table i.e. the last generated Empid in the table. (initially 0 if table has no data).
DECLARE @MaxEmpId INT;
SET @MaxEmpId= ISNULL((SELECT MAX(EmpId) FROM tbEmployeeMaster),0)
--Increment maxempid by 1 to get next emp id.
SET @MaxEmpId+=1
DECLARE @EmpCode VARCHAR(20),@i INT=1;
WHILE(@i=1)
BEGIN
--Generate new emp code of specified code length prefixed by specified prefix passed as parameters.
SET @EmpCode=@EmpPrefix +RIGHT(REPLICATE('0', @EmpLength-1) + CONVERT(VARCHAR(20),@MaxEmpId),@EmpLength)
--Check generated emp code. If already exists then get next emp code untill we get fresh emp code.
IF EXISTS(SELECT 1 FROM tbEmployeeMaster WHERE EmpCode=@EmpCode)
BEGIN
SET @MaxEmpId +=1
END
ELSE
BEGIN
SET @i=0
END
END
--Return newly generated emp code
RETURN @EmpCode
END
In above function line which actually generates the alphanumeric employee code is "SET @EmpCode=@EmpPrefix +RIGHT(REPLICATE('0', @EmpLength-1) + CONVERT(VARCHAR(20),@MaxEmpId),@EmpLength)" where @EmpCode is a variable defined in function and @EmpPrefix,@EmpLength-1 are passed variables to function. @EmpPrefix variable is concatenated with RIGHT function having two parameters, 1st parameter is REPLICATE function concatenated with CONVERT function and 2nd parameter is @EmpLength. REPLICATE function within RIGHT function will repeat the string '0' with specified number of times i.e '@EmpLength-1' times concatenate with CONVERT function converting type of @MaxEmpId to varchar. Finally RIGHT function will generate a string from rightmost side and will get appended to @EmpPrefix from rightmost side.
Finally create a stored procedure ‘spEmpDetails_Save’ where 'fnEmpCode' function is called to get auto generated alphanumeric employee code to save in table with other employee details using
following query :-
CREATE PROC spEmpDetails_Save(@EmpName VARCHAR(100))ASBEGINSET NOCOUNT ON;--Get New Emp Code by calling fnEmpCode function.DECLARE @EmpCode VARCHAR(20)=(SELECT dbo.fnEmpCode('EMP-',5))--Insert employee detail including generated emp code in tableINSERT INTO tbEmployeeMaster (EmpName, EmpCode)VALUES(@EmpName,@EmpCode);END
CREATE PROC spEmpDetails_Save
(
@EmpName VARCHAR(100)
)
AS
BEGIN
SET NOCOUNT ON;
--Get New Emp Code by calling fnEmpCode function.
DECLARE @EmpCode VARCHAR(20)=(SELECT dbo.fnEmpCode('EMP-',5))
--Insert employee detail including generated emp code in table
INSERT INTO tbEmployeeMaster (EmpName, EmpCode)
VALUES(@EmpName,@EmpCode);
END
0 Comment(s)