How can I create a unique invoice number?

jewel 581 Reputation points

I want to use a unique invoice number in my work. I am using SQL Server database.

I want an invoice number to be generated which will have current year first with autoincrement number. When the year changes to a new year, the invoice number will start over.
For example, 2023/00001
When the new year comes the invoice number will be-
This will continue.
It would be helpful if any experienced help. thanks in advance

 public class tbl_Sell
        public int SelliD { get; set; }
        public String InvoiceNO { get; set; }
        public int productID { get; set; }
        public int Qty { get; set; }
A set of technologies in the .NET Framework for building web applications and XML web services.
3,757 questions
{count} votes

Accepted answer
  1. Bruce ( 48,396 Reputation points

    if you use a table with a max value, be sure to proper locking to prevent race conditions or use a single statement:

    create table NextOrderNumber
        Year int not null,
        Number int not null
    declare @year int = 2023;
    declare @number int = 1;
    merge NextOrderNumber as n
    using (select @year as year) as y 
       on n.Year = y.Year 
    when matched then
        update set 
            Number = Number + 1,
            @number = Number + 1 
    when not matched then
        insert (Year, Number)
        values (y.year, @number);
    select cast(@year as varchar(4)) + right('0000' + cast(@number as varchar(5)), 4); 

0 additional answers

Sort by: Most helpful