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 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
Fair point. I have removed that comment. If you add a UNIQUE CONSTRAINT to your InvoiceNumber column you will prevent duplicates. — GarethD
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'

Andomar Andomar · Accepted Answer · 2012-06-07T12:27:50

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

Sequential Invoice Numbers SQL server

1 Answers