• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

SQL Code

I have a SP check the invoice numbers in the invoice table and return the next available number.
but I think thats not working good because I have a douplicate record.
so what I need to do I want to create a SP to check the number I get from the system SP and Keep it in a LOOP until it find its a uniqe number then return it to me.

any Ideas?
0
DBA2010
Asked:
DBA2010
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Atdhe NuhiuCommented:
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

Open in new window

0
 
DBA2010Author Commented:
The invoice is not an IDENTITY not a uniqe
0
 
chazsCommented:
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
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Atdhe NuhiuCommented:
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

0
 
DBA2010Author Commented:
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
0
 
SharathData EngineerCommented:
Do you have data like in my example? Check this.
declare @InvoiceTable table (InvoiceNumber int)
insert @InvoiceTable values (1),(2),(3),(2),(4),(1),(5),(5)
SELECT InvoiceNumber + Cnt Next_InvoiceNumber 
  FROM (  SELECT InvoiceNumber,COUNT(* ) Cnt 
            FROM @InvoiceTable 
           WHERE InvoiceNumber = (SELECT MAX(InvoiceNumber) 
                                    FROM @InvoiceTable) 
        GROUP BY InvoiceNumber) AS t1 -- 7

Open in new window

0
 
DBA2010Author Commented:
Yes
0
 
SharathData EngineerCommented:
>> Yes ?

Are you referring my post? Did you try that?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now