Background
SCOPE_IDENTITY gets the last identity value inserted into a column that is a defined identity in the same scope.
Scope may be a Stored Procedure, trigger, function or a batch statement.
The SCOPE_IDENTITY() function will return the most recent IDENTITY value inserted in the same scope (Stored Procedure, trigger, function or batch statement).
Note: It will return the NULL value if the function is invoked before any insert statements into an identity column in the scope.
Syntax
Return the type of the preceding function as a sql_variant.
Examples
SCOPE_IDENTITY() in select clause
- INSERT INTO [dbo].[Customers]
- ([CName]
- ,[Phone])
- VALUES
- ('Bhanu Pratap',
- '9628855006')
- SELECT SCOPE_IDENTITY()
Output
10
The preceding example returns the recently inserted identity values into the identity column named CId in the Customers table in the current session.