CODEWITHSUNDEEP
c#asp.netsql-serverentity-framework
focus
focus

Reputation: 171

Generate Sequential Invoice Number in a Multiuser Web Environment

I try to create and print invoices using web forms. I am trying to find the best recommended practice that I should use. Until now the practices I have are;

  1. Create a table that will store "next invoice number" and then generate the next number using a stored procedure, something like "next number" + 1.

  2. Store the previous number in the invoice DB table and then in my c# code increment it by one.

What I would like to avoid is use the SQL Server identity (auto increment) to generate the invoice number because of the gaps that may be happen (i have see in real life to occur ) but there are also articles that mention about these gaps. Why are there gaps in my IDENTITY column values? Identity increment is jumping in SQL Server database

My question is what is the recommended practice that I should follow to ensure that every invoice will get sequential unique invoice number?

Upvotes: 2

Views: 2952

Answers (1)

Alex
Alex

Reputation: 5157

I would recommend approach "a)".

There are two variants to it:

  1. Use SEQUENCE. Make sure that NO CACHE option is specified.
  2. As you described, create a "Counter" table that will store next ID to be assigned.

The code for it would look something like this:

CREATE TABLE Counter( NextID INT NOT NULL DEFAULT(1))
INSERT INTO Counter VALUES( DEFAULT )

DECLARE @NextID INT
UPDATE Counter
SET @NextID = NextID,
    NextID = NextID + 1

SELECT @NextID

Note: I don't fully understand your description of option "b)". Assuming that you mean to say: that you want to search current invoice table to find MAX existing invoice and then add one to it then I would strongly advise against this approach as it is fragile. If you do not create a covering index for your search query than you may end with problems due to race condition.

Upvotes: 1

Related Questions