While writing insert or update queries in the SQL server, we need to use the predefined functions of the SQL to get the current inserted or the updated record in the table.

For this we use the function called the scope identity for getting the current row.

ALTER PROC [dbo].[uspRegisterEmployee]
(
@empFirstName varchar(50),
@empMiddleName varchar(50)=NULL,
@empLastName varchar(50),
@empPhoneNo varchar(20),
@empAddress varchar(100)
)

AS


BEGIN

INSERT INTO [EmployeeDetails] ([EmpFirstName],[EmpMiddleName],[EmpLastName],[EmpPhoneNo],[EmpAddress])
VALUES (@empFirstName,@empMiddleName,@empLastName,@empPhoneNo,@empAddress)

SELECT SCOPE_IDENTITY() as EmpId
END

You can do it also from the other way but that will be not appropriate as the programmer point of view.

ALTER PROC [dbo].[uspRegisterEmployee]
(
@empFirstName varchar(50),
@empMiddleName varchar(50)=NULL,
@empLastName varchar(50),
@empPhoneNo varchar(20),
@empAddress varchar(100)
)

AS


BEGIN

INSERT INTO [EmployeeDetails] ([EmpFirstName],[EmpMiddleName],[EmpLastName],[EmpPhoneNo],[EmpAddress])
VALUES (@empFirstName,@empMiddleName,@empLastName,@empPhoneNo,@empAddress)

SELECT MAX(EmpId) as EmpId
END

We use the max function here to get the latest id from the table.