Problems implementing a 'Sequence' table

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 2005 Forums

Transact-SQL (2005)

Problems implementing a 'Sequence' table

Author

Topic

hkbloke
Starting Member

5 Posts

Posted - 2008-01-30 : 01:11:42

Hi - i'm trying to implement a Sequence table in SQLServer to generate unique identifiers for my tables similiar to the SEQUENCE model in Oracle.

After searching the net i found on old forum discussion on this topic and the solution proposed was as follows :

1) Create a simple table with a single column as follows :
create table tbl (ID int)
insert tbl select 0

2) Write a stored proc to increment and return the next sequence :
create procedure SPGetNum
@ID int output
as
update tbl set ID = ID + 1, @ID = ID + 1
go

All well and good, but i want to have a single table that maintains multiple sequences for different entities so i changed this to :

create table tbl (SEQ varchar(10), ID int)

and the stored procedure to :

create procedure SPGetNum
@SEQ varchar(10)
@ID int output
as
BEGIN
update tbl set ID=ID+1 where SEQ=@SEQ, @ID = ID+1
END

...but it doedn't like the addition of the where clause i get an 'Incorrect syntax near '@NEXT' error.

Any ideas on what I am doing wrong ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-30 : 01:18:07

quote:
Originally posted by hkbloke

Hi - i'm trying to implement a Sequence table in SQLServer to generate unique identifiers for my tables similiar to the SEQUENCE model in Oracle.

After searching the net i found on old forum discussion on this topic and the solution proposed was as follows :

1) Create a simple table with a single column as follows :
create table tbl (ID int)
insert tbl select 0

2) Write a stored proc to increment and return the next sequence :
create procedure SPGetNum
@ID int output
as
update tbl set ID = ID + 1, @ID = ID + 1
go

All well and good, but i want to have a single table that maintains multiple sequences for different entities so i changed this to :

create table tbl (SEQ varchar(10), ID int)

and the stored procedure to :

create procedure SPGetNum
@SEQ varchar(10)
@ID int output
as
BEGIN
update tbl set ID=ID+1 , @ID = ID+1 where SEQ=@SEQ
END

...but it doedn't like the addition of the where clause i get an 'Incorrect syntax near '@NEXT' error.

Any ideas on what I am doing wrong ?

Try like this and see

hkbloke
Starting Member

5 Posts

Posted - 2008-01-30 : 01:25:52

I will as soon as I get home tonight - many tks!

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-01-30 : 18:48:49

This is a really bad idea. You realise this will really screw up your concurrency and cause performance problems?
Any reason why you can't use identity columns?

hkbloke
Starting Member

5 Posts

Posted - 2008-01-30 : 19:25:16

First of all the solution works - thanks visak.

On the overall design point - in the application in question there are a number of occasions when i need to know the ID allocated to a new record so i can use it to store some secondary/tertiary data in other tables. Rather than go back and do a 'select max(id).....' etc after the insert I had the view that the sequence idea above would be a better solution.

I think the performance difference would be negligable.

What do you mean by 'concurrency problems' ?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-01-31 : 01:27:00

You can try this to

Select top 1000 identity(int,0,1) as ID into YourTable from master.dbo.syscolumns A
Select * from YourTable
Drop table YourTable

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-31 : 02:18:06

you can use an identity column for this, and it's the preferred method. using a separate sequence table not necessary.

to get the last identity value inserted, use SCOPE_IDENTITY()

elsasoft.org

hkbloke
Starting Member

5 Posts

Posted - 2008-01-31 : 02:33:58

OK - so say i have a USER table as follows :

ID as Int (this is a identity column)
NAME as varchar(20)

In my stored proc i would have :

CREATER PROCEDURE addUser
@uName as varchar(20)
AS
insert into USER(Name) values(@uName)
select SCOPY_IDENTITY()

and in my ASP.Ner page I execute "addUser 'NewUser'" and it will return a single row with a single columne being the ID it stored in the new USER record ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-31 : 02:46:17

quote:
Originally posted by hkbloke

OK - so say i have a USER table as follows :

ID as Int (this is a identity column)
NAME as varchar(20)

In my stored proc i would have :

CREATER PROCEDURE addUser
@uName as varchar(20)
AS
insert into USER(Name) values(@uName)
select SCOPY_IDENTITY()

and in my ASP.Ner page I execute "addUser 'NewUser'" and it will return a single row with a single columne being the ID it stored in the new USER record ?

Yup. it will. SCOPE_IDENTITY() returns the last ID value generated in current scope which will be the ID of last created user.

hkbloke
Starting Member

5 Posts

Posted - 2008-01-31 : 02:49:36

OK - much better way of doing it. thanks for the steer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources