SQL sequence problem

[arrian]

I need to be able to generate the next number in a sequence (9001, 9002, 9003,... ) and return it to the user. I need to be able to get the number, then the next person who queries it, will get the next number. I'm not exactly sure how to do this. I originaly thought an identity would work, but with that, I can't really send the number back. Any suggestions? If this needs clarification, let me know. I don't think I'm being quite clear here...

 

[SQLDenis]

>>I originaly thought an identity would work, but with that, I can't really send the number back.

why not?
select scope_identity() as ID (if you have triggers) or
select @@IDENTITY as ID

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> I originaly thought an identity would work, but with that, I can't really send the number back.

Yes you can - in most of situations. More info please...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[arrian]

I'm not sure what you mean with the "as ID"... As far as I can think, the only thing that's unique is the number from in the sequence.

 

[arrian]

When I use an identity, the identity is generated when the row is inserted into the table. The only unique item in the table will be the identity, so I don't know how I would be able to return the identity.

 

[SQLDenis]

as ID is the alias, this will be used as the name of the column in the resultset
here are 2 example

select getdate() as TheDateToday

select ssn as Social from table......

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[arrian]

Ok... Well, the problem is, the identity won't be generated until I've inserted the row. And there's nothing else that I can put in there that will be unique, so I'm not sure how I'll be able to return the alias. I've tried a stored procedure, and it allmost works. The only problem is it requires me to send a value to all the parameters, and it's returning the value I send, regardless of what it should be.

 

[SQLDenis]

????

Run this

create table testIdentity (id int identity , value char(1))
insert into testIdentity
select 'A'

select @@identity as ID,scope_identity() as AnotherID
go
insert into testIdentity
select 'c'
select @@identity as ID,scope_identity() as AnotherID
go

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[arrian]

Well, that could work... But the problem will be having more than one person inserting data at the same time. Will if I put the select in with the insert statement, will it allways return the id for that row?

 

[SQLDenis]

yes, this is handled by SQL Server, no duplicates possible

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/