CODEWITHSUNDEEP
asp.netsql-servert-sqlidentity-column
blue piranha
blue piranha

Reputation: 3876

Using last identity inserted value to insert in another table

I am facing this weird problem and spent several hours. Little help would be greatly appreciated. This is an ASP.NET MVC app. For simplicity, I have two SQL tables, Employee (ID, Name, JoiningDate) and Benefits (ID, EmployeeID). Both IDs are identity colums. When a new employee joins the company, an entry is created in the Employee table as well as Benefits table.

The stored procedure looks like this

 alter procedure usp_CreateEmployee
 @Name nvarchar(100),
 @JoiningDate datetime
 as
 declare @lastIdentity int
 insert into Employee(Name, JoiningDate) values(@Name, @JoiningDate)
 select @lastIdentity = ident_current('Employee')
 insert into Benefits(EmployeeID) values(@lastIdentity)

C# side I am using Dapper

 var parameters = new DynamicParameters();
 parameters.Add("@Name", name);
 parameters.Add("@JoiningDate", joiningDate);
 affectedRows = connection.Execute("usp_CreateEmployee", parameters, null, commandType: CommandType.StoredProcedure);

When I execute the stored procedure in SSMS, everything works perfect. (ident_current returns the last inserted id). However, when the user interface creates employee (through razor page), a NULL gets inserted as EmployeeID in Benefits table. Employee table shows correct Employee ID.

Doesn't look like a SQL problem. Is there anything wrong with my code? Could this be Dapper related (though I dont think so)?

Upvotes: 0

Views: 155

Answers (1)

YHTAN
YHTAN

Reputation: 686

I think the problem was on the "ident_current". Please refer here: https://sqlperformance.com/2014/01/t-sql-queries/ident-current

alternatively, you may try below sql script.

alter procedure usp_CreateEmployee
 @Name nvarchar(100),
 @JoiningDate datetime
 as
 declare @lastIdentity int
 insert into Employee(Name, JoiningDate) values(@Name, @JoiningDate)
 select top(1) @lastIdentity=ID from Employee where Name=@Name
 order by ID desc
  
 insert into Benefits(EmployeeID) values(@lastIdentity)

Upvotes: 2

Related Questions