SQL Server: Stored Procedure – Insert record and Return Identity Column value

Updated on August 30, 2015     Kisan Patel

This tutorial will show you 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 InsertRegionDetails stored procedure like below

CREATE PROCEDURE [dbo].[InsertRegionDetails]
-- Add the parameters for the stored procedure here
@RegionDesription  varchar(50),
@Age smallint
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 Region (RegionDescription,  AGE)
	VALUES (@RegionDesription, @Age)

        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 RegionId 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 data.