Sequential Invoice Numbers SQL server

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?

  • Inner join equivalent of this subquery?
  • How to setup schema compare file so that it always ignores a specific db schema?
  • SQL auto-validating data when adding it to row
  • Use a LIKE statement on SQL Server XML Datatype
  • CONTAINSTABLE and CONTAINS, which string to pass to match all records?
  • How to retrieve upload/current time in each batch file uploaded in sql server
  •  
    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

    One Solution collect form web for “Sequential Invoice Numbers SQL server”

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.