Loading
How to create Auto generate number in SQL Server
  • Bidyasagar Mishra

    Bidyasagar MishraPosted Aug 6, 2019, 9:44 PM

    1. CREATE TABLE dbo.Demo(ID INT IDENTITY PRIMARY KEY,  
    2.                       IDwithChar AS 'Req' + RIGHT('000' + CAST(ID AS VARCHAR(10)), 6) PERSISTED  
    3.                      )  
    +2
  • Sivakumar Koneti

    Sivakumar KonetiPosted Aug 6, 2019, 12:50 AM

    Read once 
    https://stackoverflow.com/questions/40175635/sql-server-auto-generated-custom-format-sequence-number
    +1
  • Amit Kumar

    Amit KumarPosted Aug 5, 2019, 11:14 PM

    Hi, you can do that. Change your proc a/c to this proc. CREATE PROCEDURE dbo.NewEmployee @EmployeeName VARCHAR(50) AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.tblEmployee(Employee_ID, Name) SELECT 'Req' + RIGHT('000' + 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
    +1
  • Rajanikant Hawaldar

    Rajanikant HawaldarPosted Aug 5, 2019, 9:41 AM

    Hi Chittaranjan, it's simple, at insert query you need to pass hardcode prefix Req and there only you can write simple query to retrieve max request number and concatenate it like example insert into table ReqNumber values (Req +(select max (ReqSerialNumber) from table name)) refer this thread https://stackoverflow.com/questions/44247945/insert-statement-select-concat
    +2
  • Priyanka Jain

    Priyanka JainPosted Aug 5, 2019, 7:14 AM

    Please refer below link. This link as explanation for the random generated number
     
    https://www.sqlteam.com/articles/custom-auto-generated-sequences-with-sql-server 
    +6
  • Sonu Gupta

    Sonu GuptaPosted Aug 5, 2019, 6:23 AM

    Try This -
     
    DECLARE @lastEmpID as VARCHAR(7)
    SET @lastEmpID = (SELECT 'Req0001') --(SELECT TOP 1 EMPID FROM tblEmp ORDER BY EMPID DESC)
    DECLARE @EmpID as VARCHAR(4)
    SET @EmpID = (SELECT RIGHT(@lastEmpID, 4) )
    DECLARE @numEmpID as INT
    SET @numEmpID = (SELECT CONVERT(INT, @empID) + 1 )
    DECLARE @NewEmployeeID as VARCHAR(7)
    IF @numEmpID < 10
    SET @NewEmployeeID = (SELECT 'Req000' + CONVERT(varchar(4),@numEmpID))
    IF @numEmpID < 100 and @numEmpID > 10
    SET @NewEmployeeID = (SELECT 'Req00' + CONVERT(varchar(4),@numEmpID))
    IF @numEmpID < 1000 and @numEmpID > 100
    SET @NewEmployeeID = (SELECT 'Req0' + CONVERT(varchar(4),@numEmpID))
    IF @numEmpID >= 1000
    SET @NewEmployeeID = (SELECT 'Req' + CONVERT(varchar(4),@numEmpID))
    SELECT @NewEmployeeID AS AutoGenerateNumber
     
    +3
  • Code Alone

    Code AlonePosted Aug 5, 2019, 5:11 AM

    Hi Chitranjan,
     
    Use below code :
    1. select 'Req'+RIGHT('00000' + cast(1 as varchar(4)), 4)   
     This will provide you : Req0001 (Which you mentioned)
    If you want to use this auto generated, then bind it in default value of that field. In that column default values write below:
    1. 'Req'+RIGHT('00000' + cast(Your_Field_Name as varchar(5)), 5)  
     Yu can you it as formula field also.
    +4
  • Shweta Lodha

    Shweta LodhaPosted Aug 5, 2019, 5:05 AM

    Hello Chittaranjan Swain,
    There are few ways to achieve this: 
    Approach 1:- Read the last inserted value from database table, before inserting the new record
     
    Approach 2 :- Let your database table column as auto incremented (identity column with data type as integer) and while displaying on UI or while using that value do the necessary formating at front end, i.e. appending Req
     
    Approach 3 :- Keep one identity column in database and another column for your values. So, one column will store 0001 and anoterh wil have formatted value Req0001
    +3