---

Search posts under SQL Server:

---

Insert record and return identity column value in SQL Server

How to create stored procedure to insert record and return identity column value?

To return the identity column value for just inserted record, we can use SCOPE_IDENTITY() method.

To see how it works, modify the InsertPersonalDetails stored procedure like below

ALTER PROCEDURE [dbo].[InsertPersonalDetails]
      -- Add the parameters for the stored procedure here
      @FirstName varchar(50),
      @LastName varchar(50),
      @Age smallint,
      @Active bit
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

   -- Insert statements for procedure here
      INSERT INTO PersonalDetails
             (FirstName, LastName, Age, Active)
      VALUES
             (@FirstName, @LastName, @Age, @Active)
      return SCOPE_IDENTITY()
END

Notice the highlighted line of code where we are using RETURN keyword with the SCOPE_IDENTITY() method. When we execute this stored procedure, after inserting the record, it returns the PersonalDetialsId value of last inserted record.

Now running the modified stored procedure gives a return value like below

Important

SCOPE_IDENTITY() method only returns the Identity (auto increment) column value. If the primary key value is of different type, it will not return desired result.