Error: Select scope_identity() function

Problem: Suppose I have a table consisting of a primary key (ID) and some fields. I want to (from an ASP page) insert some data entered by a user from the table, and then retrieve the ID from the record that was just inserted. Then that ID will be used in another table to tie the two tables together. How do I write the SQL to do this????

Solution: use the scope_identity() function

Example: declare @id = int
select @id = scope_identity()

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

Its best to use scope_identity() , because, it returns values inserted only within the current scope.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

ident_current does not work within the scope of the transaction. So if you have multiple transactions, it might give you an error