Sequential Invoice Numbers SQL server

Question

I have an invoice table that has InvoiceID (int identity ) Primary key. and a column InvoiceNumber which is an interger. I have another table i use to generate the invoice number called Invoice_Numbers (see below). To ensure that the invoice number is unique and prevented gaps i have implemented the code below. Can some one review this code and comment on its reliability. Running SQL 2008 in multi-user environment.

What are the chances of users getting the same invoice number when they call then procudere during insert of invoices?

 
IF EXISTS
   (SELECT *
    FROM   sys.objects
    WHERE  object_id = OBJECT_ID(N'[Imports].[Invoices_Numbers]')
           AND type IN ( N'U' ))
  DROP TABLE [Imports].[Invoices_Numbers]

GO

CREATE TABLE [Imports].[Invoices_Numbers]
  (
     [InvoiceNumber] [INT] IDENTITY(1, 1) NOT NULL
     ,[Deleted]      [BIT] NOT NULL
     ,[Used]         [BIT] NOT NULL,
     CONSTRAINT [PK_Invoices_Numbers] PRIMARY KEY CLUSTERED ( [InvoiceNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  )
ON [PRIMARY]

GO

ALTER PROCEDURE [Imports].[Get_Invoice_Number]
(
  @InvoiceNumber INT OUTPUT
)
AS
  BEGIN
      DECLARE @NewNumber INT
      DECLARE @MinNumber INT

  BEGIN TRAN

  SELECT @MinNumber = MIN(InvoiceNumber)
  FROM   Imports.Invoices_Numbers

  IF @MinNumber > 1
    BEGIN
        SET IDENTITY_INSERT Imports.Invoices_Numbers ON;

        INSERT Imports.Invoices_Numbers
        (
          Invoicenumber
          ,Deleted
          ,Used
        )
        VALUES
        (
          1
          ,0
          ,1
        )
        SET IDENTITY_INSERT Imports.Invoices_Numbers OFF;
        SET @NewNumber=1
    END
  ELSE
    BEGIN
        WITH Gaps
             AS (SELECT TOP 1 a.InvoiceNumber + 1 AS GapValue
                 FROM   Imports.Invoices_Numbers a
                 WHERE  NOT EXISTS
                            (SELECT *
                             FROM   Imports.Invoices_Numbers b
                             WHERE  b.InvoiceNumber = a.InvoiceNumber + 1)
                        AND a.InvoiceNumber <
                            (SELECT MAX(InvoiceNumber)
                             FROM   Imports.Invoices_Numbers))
        SELECT @NewNumber = GapValue
        FROM   Gaps

        IF @NewNumber IS NULL
          BEGIN
              SELECT TOP 1 @NewNumber = InvoiceNumber
              FROM   Imports.Invoices_Numbers
              WHERE  Used = 0
                     AND Deleted = 0
              ORDER  BY InvoiceNumber

              IF @NewNumber IS NULL
                BEGIN
                    INSERT Imports.Invoices_Numbers
                    (
                      Deleted
                      ,Used
                    )
                    VALUES
                    (
                      0
                      ,1
                    )
                    SELECT @NewNumber = SCOPE_IDENTITY ()
                END
              ELSE
                BEGIN
                    UPDATE Imports.Invoices_Numbers
                    SET    Used = 1
                    WHERE  InvoiceNumber = @NewNumber
                END
          END
        ELSE
          BEGIN
              SET IDENTITY_INSERT Imports.Invoices_Numbers ON;

              INSERT Imports.Invoices_Numbers
              (
                Invoicenumber
                ,Deleted
                ,Used
              )
              VALUES
              (
                @NewNumber
                ,0
                ,1
              )
              SET IDENTITY_INSERT Imports.Invoices_Numbers OFF;
          END
    END

  SELECT @InvoiceNumber = @NewNumber

  COMMIT TRAN

END

See SO Question How would you implement sequences in Microsoft SQL Server? — Olivier Jacot-Descombes
– Olivier Jacot-Descombes, Commented Jun 7, 2012 at 12:02
Olivier just gone through the link. However it doesnt address the key issues im trying to address. First the environment is multi-user so possibility of proc. being called simultenously is there. Second im using a second table to generate invoice numbers(serially) where i ensure if something went wrong the gaps are filled. Pay closer lookup at the proc. and see what im attempting to do. — Clarence Assey
– Clarence Assey, Commented Jun 7, 2012 at 12:18
Fair point. I have removed that comment. If you add a UNIQUE CONSTRAINT to your InvoiceNumber column you will prevent duplicates. — GarethD
– GarethD, Commented Jun 7, 2012 at 12:21
The term "sequence" seems to be throwing everyone off. This issue has to do with accounting--invoice numbers, order numbers, raffle ticket numbers, and so on need to be doled out to multiple, concurrent users, and the accountants need to know what happened to each and every one of them. It's usually deemed necessary that they be issued in sequential order (hence, no gaps), but that's really application-dependent. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Jun 7, 2012 at 12:25

Andomar · Accepted Answer · 2012-06-07 12:27:50Z

1

Your solution looks a bit complex. I wouldn't recomment using set identity_insert for anything other than data importing tasks.

To ensure uniqueness, I'd first add a unique constraint:

alter table Invoices add constraint UX_Invoices_InvoiceNr unique

Then you could use a SQL statement like this to insert an invoice:

while 1=1
    begin        
    declare @new_nr int
    select  @new_nr = max(InvoiceNr) + 1
    from    dbo.Invoices

    if @new_nr is null
        set @new_nr = 1

    insert  dbo.Invoices
            (InvoiceNr, ...)
    values  (@new_nr, ...)

    if @@rowcount = 1
        break
    end

answered Jun 7, 2012 at 12:27

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Clarence Assey Over a year ago

Looks more elegant. Let me try your approach and will let you know

Clarence Assey Over a year ago

Thanks a lot works great and ensures no gaps. I made a sample table and tried a bit of twisting and turning and it seemed to give me exactly what i needed. thanks again!!!

Clarence Assey Over a year ago

Just to add for anyone who will use this approach. You have to make sure you do not delete invoices as this will create a problem since the numbers you are getting are based on row count. I have opted to mark invoices deleted rather than actually deleting them.

Collectives™ on Stack Overflow

Sequential Invoice Numbers SQL server

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related