Identities / Auto Incrementing Indexes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Identities / Auto Incrementing Indexes

Author

Topic

SimonGough
Starting Member

23 Posts

Posted - 2002-01-07 : 06:32:48

Hi,
I want to create a unique, sequential index for a product. The user opens a form. The next unique id (e.g. 99129) is displayed by creating and then pulling it from the database.

The user then inserts the new product and then the product ID just created is applied to the unique id 99129.

If the user cancels the process before inserting a product, the unique ID 129 is deleted and cannot be used again. The next time a product is inserted, unique id of 99130 will be applied to it.....and so on.

How do I do this in SQL Server? I have a seperate table for the unique id and it's corresponding product ID to live in but I'm having trouble incrementing the unique id to initially display for the user which is set as an index/primary key in it's table.

Thanks for any help. I hope I have explained what I want to do well enough.

Simon.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-07 : 06:40:41

From the question I guess you have the ID table as an identity and are inserting into it.

something like
create table nextid (id int identity(1,1), user varchar(128))
insert nextid (user) select 'me'

to get the identity just
declare @id int
select @id = @@identity

Just return this to the form and the form returns it to insert the product.
For this though I probably wouldn't use an identity but have an SP that increments the ID and returns the next one.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Nazim
A custom title

1408 Posts

Posted - 2002-01-07 : 08:20:52

something like this
Create Procedure sp_NextValue
begin tran
select nextval from table
update table set nextval = nextval+ 1
commit tran

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

SimonGough
Starting Member

23 Posts

Posted - 2002-01-07 : 08:48:04

Thanks guys.....got it sorted now.

Simon

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-07 : 08:59:35

Actually something more like (particularly not calling the sp sp_)

Create Procedure spNextValue
@id int output
as
update table
set @id = nextval + 1 ,
nextval = nextval+ 1
go

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Edited by - nr on 01/07/2002 09:00:10

Nazim
A custom title

1408 Posts

Posted - 2002-01-07 : 09:35:12

Thanx for correcting it nr . i messed tht up, it looks like a system stored procedure.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources