我做了一个更新个人信息的存储过程,每次都是执行错误,我没有检查出来,大家给指点一下。
我先检查除不出现账号重复,即就是检查主键不等于@EmpID的记录,然后再进行更新。 SELECT * FROM EmpInfo WHERE LoginID=@LoginID And ((empID>@EmpID) or (empID<@EmpID))
IF @@rowcount > 0
RETURN 0 --该帐号已经被使用
update EmpInfo
set
LoginID = @LoginID ,
EmpName = @EmpName ,
EMail = @EMail ,
Sex = @Sex ,
OfficeTel = @OfficeTel ,
HomeTel = @HomeTel ,
Mobile = @Mobile ,
Address = @Address,
Birthday=@Birthday,
Password=@Password,
Oicq=@Oicq
where empID = @EmpID
return
我先检查除不出现账号重复,即就是检查主键不等于@EmpID的记录,然后再进行更新。 SELECT * FROM EmpInfo WHERE LoginID=@LoginID And ((empID>@EmpID) or (empID<@EmpID))
IF @@rowcount > 0
RETURN 0 --该帐号已经被使用
update EmpInfo
set
LoginID = @LoginID ,
EmpName = @EmpName ,
EMail = @EMail ,
Sex = @Sex ,
OfficeTel = @OfficeTel ,
HomeTel = @HomeTel ,
Mobile = @Mobile ,
Address = @Address,
Birthday=@Birthday,
Password=@Password,
Oicq=@Oicq
where empID = @EmpID
return
CREATE PROCEDURE hrUpdateEmpInfo
@EmpID int ,
@LoginID varchar(50),
@EmpName varchar(50),
@EMail varchar(100),
@Sex bit,
@OfficeTel varchar(20)=null,
@HomeTel varchar(20)=null,
@Mobile varchar(20)=null,
@Address varchar(100)=null,
@Birthday datetime=null,
@Password varchar(50)=null,
@Oicq varchar(20)=null
AS
SELECT * FROM EmpInfo WHERE LoginID=@LoginID And ((empID>@EmpID) or (empID<@EmpID))
IF @@rowcount > 0
RETURN 0 --该帐号已经被使用
update EmpInfo
set
LoginID = @LoginID ,
EmpName = @EmpName ,
EMail = @EMail ,
Sex = @Sex ,
OfficeTel = @OfficeTel ,
HomeTel = @HomeTel ,
Mobile = @Mobile ,
Address = @Address,
Birthday=@Birthday,
Password=@Password,
Oicq=@Oicq
where empID = @EmpID
return
GO
update EmpInfo
set
LoginID = @LoginID ,
EmpName = @EmpName ,
EMail = @EMail ,
Sex = @Sex ,
OfficeTel = @OfficeTel ,
HomeTel = @HomeTel ,
Mobile = @Mobile ,
Address = @Address,
Birthday=@Birthday,
Password=@Password,
Oicq=@Oicq
where empID = @EmpID
if not exists (select 1 FROM EmpInfo WHERE LoginID=@LoginID And empID <> @EmpID)
但是还是不能正常执行,myCommand.ExecuteNonQuery()不能正常执行,为什么?
@EmpID int ,
@LoginID varchar(50),
@EmpName varchar(50),
@EMail varchar(100),
@Sex bit,
@OfficeTel varchar(20)=null,
@HomeTel varchar(20)=null,
@Mobile varchar(20)=null,
@Address varchar(100)=null,
@Birthday datetime=null,
@Password varchar(50)=null,
@Oicq varchar(20)=null
ASSELECT * FROM EmpInfo WHERE LoginID=@LoginID And ((empID>@EmpID) or (empID<@EmpID))
IF @@rowcount > 0
--RETURN 0 --该帐号已经被使用
update EmpInfo
set
LoginID = @LoginID ,
EmpName = @EmpName ,
EMail = @EMail ,
Sex = @Sex ,
OfficeTel = @OfficeTel ,
HomeTel = @HomeTel ,
Mobile = @Mobile ,
Address = @Address,
Birthday=@Birthday,
Password=@Password,
Oicq=@Oicq
where empID = @EmpID
go