@@IDENTITY doesn't work

[qwer]

I am trying to get the last ID auto in MS SQL 2000

SELECT @@IDENTITY as ident FROM users

and i get nothing

i am using

SELECT MAX(id) as ident from users

what is the correct way to be shure to get at once the real last ID ?

thank you

[Rawhide]

Okay, a word about fetching identity values. There are three basic ways to do it.

"Select @@IDENTITY" returns the last identity value created by the current session in any scope.

"Select SCOPE_IDENTITY( )" returns the last identity value created by the current session in the current scope.

"Select IDENT_CURRENT('table_name')" returns the last identity value for the specified table from any session and any scope.

[qwer]

then if i have juste inserted a new row what is the best way to get the new id created ?

thank you

[skhanal]

select scope_identity() is safest, but if you are retrieving the value out of its scope then it will not return anything. In that case you have use @@identity.

Since you are getting value immediately after insert, scope_identity() will work perfectly for you.

[qwer]

I'll try scope_identity
thank you