使用存储过程插入数据
/****** Object: StoredProcedure [dbo].[sp_Insert_Student] Script Date: 2016/7/14 21:19:34 ******/
CREATE proc [dbo].[sp_Insert_Student](
@No char(10),
@Name varchar(20),
@Sex char(2),
@Age int,
@rtn int output
)
as
declare
@tmpName varchar(20),
@tmpSex char(2),
@tmpAge int
if exists(select * from Student where [No]=@No)
begin
select @tmpName=Name,@tmpSex=Sex,@tmpAge=Age from Student where [No]=@No
if((@tmpName=@Name) and (@tmpSex=@Sex) and (@tmpAge=@Age))
begin
set @rtn=0 --有相同的数据,直接返回
end
else
begin
update Student set Name=@Name,Sex=@Sex,Age=@Age where [No]=@No
set @rtn=2 --有主键相同的数据,进行更新处理
end
end
else
begin
insert into Student values(@No,@Name,@Sex,@Age)
set @rtn=1 --没有相同的数据,进行插入操作
end
GO