SCOPE_IDENTITY()

Recently I was checking into some SQL Queries where I ran into this function called as SCOPE_IDENTITY()..Knowing I need to investigate  I asked my collegue and also google about what did the function basically do.

Answer: Returns the last identity value inserted into an identity column in the same scope( eg stored procedure)

Lets consider a stored procedure which inserts data into a particular table and say I want to know the value of the last record inserted 

 CREATE  PROCEDURE [dbo].[customerinsert]
(
  @CustomerName  VARCHAR(50),

  @CustomerEmail  VARCHAR(50),

  @CustomerAdrress  VARCHAR(50),

  @CustomerID  INT OUT

)
AS
BEGIN
        

Insert into Customer(Name,Eamil,Address)

values(@CustomerName,  @CustomerEmail ,@CustomerAdrress)

    

SET @CustomerID = CAST(SCOPE_IDENTITY() AS INT)

END

 As one can see SCOPE_IDENTITY() is used and the value is assigned to Customer id as a Output parameter which returns the value.