Using Sequences like the IdentityColumn in SQL server

I am not a database newbie - but I am very much an Oracle newbie.

So, that in mind...

With SQL server there is no Sequence, there is a data-type called Identity, which works like a sequence for each table. You don't Insert that field, it just auto increments (It is an Integer).

So:

Insert into Users(FirstName, LastName)
Values ('Sandy', 'Murdock')

Could actually insert:

UserID 4544 (the Identity Column)
firstName = Sandy
LastName = Murdock

You don't tell it to insert the unique value, it just does.

If you want to know the value of the Identity Column you just inserted you can type:

select @@Identity

in your stored procedure, so you could type:

select @UserID = @@Identity

after inserting a row in a User table, and the variable @UserID would be equal to the Identity you just inserted.

With a sequence in a stored procedure, how can equate the sequence.currval to a variable??

I want to use the value in the where clause of a Statement, which you cannot??? do with the sequence.currval.

Any suggestions????

Believe it or not I really DO know what I am doing with MS SQL server ;)

Thank you. Received on Wed Nov 21 2001 - 19:21:28 CST