SCOPE_IDENTITY is used to get last identity value inserted into an identity column in the same scope. Scope can be stored procedure, trigger, function or batch.
SCOPE_IDENTITY() will return the IDENTITY value inserted, which was the last INSERT that occurred in the same scope.
The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.
Syntax of SCOPE_IDENTITY() Function :
SCOPE_IDENTITY()
Return type of above function is a sql_variant.
Examples of SCOPE_IDENTITY() Function :
Example 1 : Use of SCOPE_IDENTITY() function in select clause
INSERT INTO [Northwind].[dbo].[Shippers]
([CompanyName]
,[Phone])
VALUES
('Load Runner',
'(503) 555-9830')
SELECT SCOPE_IDENTITY()
Output
4
Above example returns lastly inserted identity values in identity column name ShipperID in the Shippers table in current session.