Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Code

Posted on 2011-05-11
8
Medium Priority
?
187 Views
Last Modified: 2012-05-11
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
Comment
Question by:DBA2010
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 35738862
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
 

Author Comment

by:DBA2010
ID: 35738947
The invoice is not an IDENTITY not a uniqe
0
 
LVL 1

Expert Comment

by:chazs
ID: 35738978
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 35739012
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
 

Author Comment

by:DBA2010
ID: 35739920
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
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35744153
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
 

Author Comment

by:DBA2010
ID: 35747390
Yes
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35748234
>> Yes ?

Are you referring my post? Did you try that?
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question