Get the field value after INSERT

[Eugene]

Hi,

Problem:
I need to get the value of auto-incremented field from just inserted
record

In Oracle this is INSERT .. RETURNING command.
In SQL Server there are @@IDENTITY, IDENT_CURRENT, SCOPE_IDENTITY

- @@IDENTITY returns the value from the very LAST insert on any table
involving in the insert process ( including triggers ) ,
so this value may have nothing to do with my table

- IDENT_CURRENT returns the last identity value generated for a
specific table in any session and any scope,
so this value may come not from my session

- SCOPE_IDENTITY returns the last identity value generated for any
table in the current session and the current scope , but from the very
LAST insert command ( that may be some INSERT in the audit tables)
so it may have nothing to do with my table

Question :
- Is there any trusted way I can get the value of auto-incremented
field
in my table and in the scope of my session?
Thanks, Eugene

[David Portas]

Why doesn't SCOPE_IDENTITY() meet your requirements? Retrieve it
immediately after the INSERT in the current scope. If you also insert
to audit tables then you just need to retrieve SCOPE_IDENTITY() before
that insert rather than after. You can assign the value to a variable
and then return that variable as an output parameter from a stored
procedure if you need to.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Eugene (yg********@hotmail.com) writes:

- @@IDENTITY returns the value from the very LAST insert on any table
involving in the insert process ( including triggers ) ,
so this value may have nothing to do with my table
Not any table, only inserts to table that has an IDENTITY column matters.
Which may be problematic rnough.
- IDENT_CURRENT returns the last identity value generated for a
specific table in any session and any scope,
so this value may come not from my session
Yes.
- SCOPE_IDENTITY returns the last identity value generated for any
table in the current session and the current scope , but from the very
LAST insert command ( that may be some INSERT in the audit tables)
so it may have nothing to do with my table

As David said, retrieve the value directly after the INSERT you are
interested in.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp