[thelist] Selecting next available number

Brooking, John John.Brooking at sappi.com
Fri Mar 4 08:38:06 CST 2005


Les says:
> I need to supply a unique id for display on screen at the *start* 
> of an order process. The id should be the next available within a 
> range, eg 1234000000 to 1234999999.
> 
> I currently do this by finding the max(id) for that range in a db 
> table and adding one.
> 
> I have two issues which I am not sure how to deal with.

Google "sql server autonumber".

Not sure if you can specify the range, but you could always add
1234000000 to the generated number. You are right that there will be
concurrency and pooling issues with the simple "select max + 1" method.
That's why it has to be something built in to the DB system, not
application code.

I'm pretty sure there is also some special syntax to do an insert and
have the assigned key returned in the same step. Don't know the syntax
off the top of my head ("RETURNING fieldname" maybe?), but that should
be easily googleable too.

If you need this number right away to start an order, but don't want to
actually create the official order until the customer finishes, I would
consider creating a record in an intermediate table for "started
orders", where this key is assigned as an autonumber. Then at the end of
the process, copy the record into the official orders table, using the
same number into a normal numeric field. You may want to clean up your
"started orders" table occasionally so it doesn't get too bloated with
abandoned orders.

- John
-- 


This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.



More information about the thelist mailing list