1存储过程中的插入和更新问题 [英] Problem in Insert and Update in 1 Store Procedure

问题描述

我试图在一个Store程序中插入和更新记录，我的EmployeeId字段是自动生成的，问题是当我从我使用它时aspx页面说它没有提供@EmployeeID。

代码是：

Hi,

I am trying to Insert and update records in one Store procedure,My EmployeeId field is auto generated, the problem is when i use it from my aspx page it says @EmployeeID not supplied.
Code is:

create PROCEDURE usp_insertupdatepragimcrud
@EmployeeID int,
@Name varchar(50),
@Gender varchar(10),
@City varchar(50)
as begin
if not exists(select * from dbo.tblEmployeeDataAccessLayer where employeeid=@EmployeeID)
insert into tblEmployeeDataAccessLayer (Name,Gender,City) values (@Name,@Gender,@City)
else 
update tblEmployeeDataAccessLayer set Name=@Name,Gender=@Gender,City=@City where employeeid=@EmployeeID  
end

如何修复它，因为员工ID将自动生成。

How do i fix it, as Employee id will be Auto generated.

推荐答案

要解决您的问题，请将EmployeeId标记为n如果你没有通过员工ID，那么从前端可以获得ullable，那么它就可以工作。



更好的解决方案：



而不是检查SPIsExit，从@EmployeeId传递，以防你要更新，否则它将被插入。

所以你只需要检查

if（@Employee id为NULL）

然后插入你的记录

else

更新你的记录

To fix your problem, mark EmployeeId as nullable so from front end if you don't pass employee id then it will work.

For better solution:

Instead checking in SP "IsExit", pass from the @EmployeeId in case you are going to Update otherwise it will be inserted.
So you need to check only
if(@Employee id is NULL)
then Insert your record
else
Update your record

您的存储过程有一个EmployeeID参数，因为您在存储过程中使用它来检查现有的员工ID，所以需要提供该参数。



您应该检查员工是否按名称而不是员工ID存在。否则，您必须将员工ID传递给存储过程。

Your stored procedure has an argument for EmployeeID, this needs to be supplied since you use it in the stored procedure to check for the employee id existing.

You should be checking if the employee exists by name instead of employee ID. Otherwise you have to pass in an employee id to the stored procedure.

这篇关于1存储过程中的插入和更新问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！