Using SCOPE_IDENTITY() to return the last identity value from an database insert in the SQL Server

The following example shows you how to use the SCOPE_IDENTITY() function to return the identity value of the row just inserted with an SQL INSERT in the SQL Server.

CREATE PROCEDURE [dbo].[SaveCustomer](
    @Name VARCHAR(255),
    @Email VARCHAR(255),
    @Phone VARCHAR(255))
AS
BEGIN
  DECLARE  @CustomerID INT
    INSERT INTO dbo.Customer (Name, Email, Phone)
    VALUES (@Name,@Email,@Phone)
   
    SELECT  @CustomerID = Scope_identity()
 END

The CustomerID is the identity column in the Customer table.

CREATE TABLE dbo.Customer (
  CustomerID INT IDENTITY( 1  , 1  ) PRIMARY KEY,
  Name VARCHAR(255) NOT NULL,
  Email VARCHAR(255) NOT NULL,
  Phone VARCHAR(255) NULL)