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?
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]
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]
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
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