Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Auto Generated Auto Incremented Alphanumeric Sequential Code in SQL SERVER

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 244
    Comment on it

    Create a table tbEmployeeMaster by following query :-

     

    CREATE TABLE tbEmployeeMaster
    (
                EmpId  INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
                EmpName VARCHAR(100),
                EmpCode VARCHAR(15)
    )

     

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

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: