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.
Views: 36776 | Post Order: 93