Get the field value after INSERT

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, EugeneWhy 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
--|||Eugene (ygorelik20@.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, esquel@.sommarskog.se

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