Stored procedure to insert a new employee and return the newly created record (including the Id)

Question

CREATE PROCEDURE InsertEmployee
@RFC varchar(15), 
@FirstNames varchar(150), 
@LastNames varchar(150), 
@DOB datetime, 
@IdPosition bigint, 
@ReportsTo bigint, 
@IdBranch bigint, 
@Salary money
AS
INSERT INTO Employee
( 
    RFC, 
    FirstNames, 
    LastNames, 
    DOB, 
    IdPosition, 
    ReportsTo, 
    IdBranch, 
    Salary
)
VALUES(@RFC, @FirstNames, @LastNames, @DOB, @IdPosition, @ReportsTo, @IdBranch, @Salary)
SELECT TOP 1 * FROM Employee ORDER BY IdEmployee DESC

How can I do this without the SELECT TOP 1, since this could cause trouble with multiple queries at the same time with the ID

Answer 1

I guess This could be the easiest method .

CREATE PROCEDURE InsertEmployee
@RFC varchar(15), 
@FirstNames varchar(150), 
@LastNames varchar(150), 
@DOB datetime, 
@IdPosition bigint, 
@ReportsTo bigint, 
@IdBranch bigint, 
@Salary money
AS

begin

Declare @Employee_pk bigint

INSERT INTO Employee
( 
    RFC, 
    FirstNames, 
    LastNames, 
    DOB, 
    IdPosition, 
    ReportsTo, 
    IdBranch, 
    Salary
)
VALUES(@RFC, @FirstNames, @LastNames, @DOB, @IdPosition, @ReportsTo, @IdBranch, @Salary)

set @Employee_pk=SCOPE_IDENTITY()

select * from EMPLOYEE where id=@Employee_pk


end