SQL Code

I have a table that stores the invoice number. It is an identity field

The stored procedure gets the next available number and thus ensures uniqueness.

See code

CREATE TABLE [Invoice](
	[InvoiceNumber] [int] IDENTITY(700001,1) NOT NULL,
 CONSTRAINT [PK_Invoice] 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]


CREATE PROCEDURE [dbo].[GetInvoiceNumber] 
@Result as varchar(20) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @InvoiceNumber as int

  insert into dbo.Invoice DEFAULT VALUES

  SELECT @InvoiceNumber = SCOPE_IDENTITY()
  SELECT @Result = CAST(@InvoiceNumber as varchar(20))

  Delete dbo.Invoice where InvoiceNumber < @InvoiceNumber
END

Select allOpen in new window

The invoice is not an IDENTITY not a uniqe

one way is try using an autonumber, intead of doing it on sp,
also you can try is create another table of invoices numbers something like this
invoice number  status
1                            close
2                            close
3                             open
4                             free

every time you do a max(invoice number) + 1 where status is free if there is not insert a new one on the table, if the invoice is complete susscecfolly mark it as close, if not free the record, with this way, you won't lose numbers and will not going to have integrity problems.

hope some of this helps you

regards

Identity is an auto incremented field and is therefore unique. What I have posted is a method for generating consective unique numbers which is the usual requirement for invoice numbers

I have this code but still duplicate the numbercan you help with this?


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[invoiceno]
as
declare @invoiceno varchar(24)      
execute dbo.getinvoiceNumber 'invoiceNo', @OrderNo Output
select  @invoiceNo
WHILE (SELECT invoiceno from dbo.InvoiceTable where invoiceno = @invoiceNo) is not null
BEGIN
if (SELECT invoiceno from dbo.InvoiceTable where invoiceno = @InvoiceNo) is null
BREAK
ELSE
execute dbo.getInvoiceNumber 'invoiceNo', @invoiceNo Output
CONTINUE
select  @invoiceNo
END

Yes

>> Yes ?

Are you referring my post? Did you try that?