GRAB UNIQUE ASCENDING NUMBER FROM DB TABLE

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
pjordannaAsked:
Who is Participating?
 
TimCotteeHead of Software ServicesCommented:
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
 
adatheladCommented:
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
 
geotigerCommented:

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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
TimCotteeHead of Software ServicesCommented:
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
 
pjordannaAuthor Commented:
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
 
TimCotteeHead of Software ServicesCommented:
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
 
pjordannaAuthor Commented:
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
 
pjordannaAuthor Commented:
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
 
jrb1Commented:
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
 
jrb1Commented:
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
 
adatheladCommented:
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
 
jrb1Commented:
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
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.

All Courses

From novice to tech pro — start learning today.