?
Solved

GRAB UNIQUE ASCENDING NUMBER FROM DB TABLE

Posted on 2005-05-11
15
Medium Priority
?
181 Views
Last Modified: 2010-03-19
Hi Experts,

I have a website which produces many quotes. I need to give each quote a unique number. My idea is to have a DB table called quoteNo with a single numeric field in it. When each quote is requested the system will retrieve the current number, add 1 to it, save this as the new number and update it in the table, whislt also saving the value into an ASP variable. This then becomes the current quote number.

I am worried about duplicate numbers being issued though. Is there a way I can accomplish all this whislt the table is "locked out" so i don't get any duplicate quote numbers?

Assume a starting quote number of "1"


Thanks,



PJORDANNA
0
Comment
Question by:pjordanna
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 2000 total points
ID: 13976698
Hi pjordanna,

You could do it with a GUID rather than an integer, alternatively you could use an identity field in a table:

Create Procedure GetQuoteNo(@QN int OUTPUT) As

Insert Into QuoteNo (QuoteDT) Values(GetDate()) -- Inserts the date the quote was stored, also increments identity field
Set @QN = SCOPE_IDENTITY -- Returns the value of the identity field in the new record

This will always generate a unique incrementing number for you

Tim Cottee
0
 
LVL 23

Expert Comment

by:adathelad
ID: 13976723
As Tim said, you can use an IDENTITY field to auto generate an incrementing number for you - this is a better approach than generating it manually as you'd have to make sure that no 2 processes (if run at the same time) end up with the same unique id (i.e. you'd have to use a serializable transaction isolation level).

You can always format the identity value to a standard form for display purposes (e.g. Q0000001)
0
 
LVL 12

Expert Comment

by:geotiger
ID: 13977117

You can do it without identity as well

Create Procedure GetQuoteNo(@uid int OUTPUT) As
DECLARE @uid int
@uid =  max(QuoteID)+1 from QuoteNo           -- get next number
Insert into QuoteNo (QuoteID) VALUES (@uid)  -- save the next number, so no one else can use it
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 43

Expert Comment

by:TimCottee
ID: 13977170
geotiger,

As adathelad said, doing it this way requires you to set an appropriate isolation level, it is possible that with your method you would still get duplicate numbers returned.

Tim
0
 

Author Comment

by:pjordanna
ID: 13984747
Hi Guys,

Thanks for the posts.

Firstly I am not experienced in stored proceedures so can anyone help me in wrapping Tim's example in an SP?

Also Tim - does your method add a new row each time a quote is requested? I was hoping to have just one row where the number incremented for each quote.



Thanks,



PJORDANNA
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 13987304
It does add a new row, you could add some additional fields here to help you manage the content of the table as well. Depending on your architecture, you could perhaps store whether the quote was ever actioned and can then remove this from the table.

It already is a stored procedure, you can copy/paste this and then call it from your application.
0
 

Author Comment

by:pjordanna
ID: 13987381
Tim,

I have set up a table as follows:

Table name = QuoteNo

Fields:

QuoteDT - smalldatetime
SCOPE_IDENTITY - numeric (identity = yes)


When I try and save this stored proceedure I get the following error - invalid column name "SCOPE_IDENTITY"


Any ideas?




PJORDANNA



0
 

Author Comment

by:pjordanna
ID: 13988245
OK,

I now have the proceedure saving correctly as follows:


CREATE Procedure ah_quoteNoProc(@QN int OUTPUT) As

Insert Into ah_quoteNo (QuoteDate) Values(GetDate()) -- Inserts the date the quote was stored, also increments identity field
Set @QN = @@IDENTITY -- Returns the value of the identity field in the new record



But how do I execute it and return a value?



PJORDANNA
0
 
LVL 25

Expert Comment

by:jrb1
ID: 14003044
sorry I'm getting in late.  Couldn't you just have your table ah_quoteNo with a single column quoteNo, and you have 1 row in the table containing the latest quote number generated.  When you need a new number, execute:

update ah_quoteNo
set @qn = quoteNo = quoteNo + 1;

Your variable would have the new number and the table would contain the same number.  Normal locking should cause this to work OK.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 14237703
I think the answer is here...many options, examples, etc.  I'm not sure which answers were most helpful, but several should have been.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 14238447
My opinion is that points go to TimCottee. His solution is the standard/easiest/most reliable way of doing it. The other solutions are feasible alternatives but could lead to issues if not implemented correctly (i.e. duplicate Ids)
0
 
LVL 25

Expert Comment

by:jrb1
ID: 14242218
Well, mine won't ever allow duplicates because of the locking mechanism--and it's a 2 line solution as to creating procedures, etc.  I can't comment on anyone else's.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 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