C
cryingfox
Unregistered / Unconfirmed
GUEST, unregistred user!
遇到的问题是这样的，我需要根据记录关键字是否存在来选择Insert或者Update，用户不一定把所有的字段都输入，所以我给存储过程的参数都设置了Null的缺省值，造成的后果是在数据库中存在记录关键字进行update的时候，可能会把原来有数据的一些字段改写成Null了，各位老大有遇到这种问题吗，真是头疼啊，分不够的话可以加。 我把存储过程贴在下面:
CREATE PROCEDURE InsertUpdateStudent @StudentID varchar(20), @Name varchar(20) = null, @Sex varchar(10) = null, @Birthday datetime =null,
@Password varchar(20) =null, @DepartmentID varchar(20) =null, @InstituteID varchar(20) =null, @Major varchar(20) =null, @ClassID varchar(20) =null,
@GradeID varchar(20) =null, @Photo image = null AS
IF EXISTS(SELECT * FROM Student WHERE StudentID = @StudentID)
UPDATE Student SET Name = @Name, Sex = @Sex , Birthday = @Birthday,
Password = @Password, DepartmentID = @DepartmentID, InstituteID = @InstituteID,
Major = @Major, ClassID = @ClassID , GradeID = @GradeID, Photo = @Photo WHERE StudentID = @StudentID
ELSE
INSERT INTO Student (StudentID, Name, Sex , Birthday, Password, DepartmentID, InstituteID, Major, ClassID , GradeID, Photo)
VALUES (@StudentID, @Name, @Sex , @Birthday, @Password, @DepartmentID, @InstituteID, @Major, @ClassID , @GradeID, @Photo)
GO
CREATE PROCEDURE InsertUpdateStudent @StudentID varchar(20), @Name varchar(20) = null, @Sex varchar(10) = null, @Birthday datetime =null,
@Password varchar(20) =null, @DepartmentID varchar(20) =null, @InstituteID varchar(20) =null, @Major varchar(20) =null, @ClassID varchar(20) =null,
@GradeID varchar(20) =null, @Photo image = null AS
IF EXISTS(SELECT * FROM Student WHERE StudentID = @StudentID)
UPDATE Student SET Name = @Name, Sex = @Sex , Birthday = @Birthday,
Password = @Password, DepartmentID = @DepartmentID, InstituteID = @InstituteID,
Major = @Major, ClassID = @ClassID , GradeID = @GradeID, Photo = @Photo WHERE StudentID = @StudentID
ELSE
INSERT INTO Student (StudentID, Name, Sex , Birthday, Password, DepartmentID, InstituteID, Major, ClassID , GradeID, Photo)
VALUES (@StudentID, @Name, @Sex , @Birthday, @Password, @DepartmentID, @InstituteID, @Major, @ClassID , @GradeID, @Photo)
GO