alter proc WorkInfo
as
begin transaction
if exists(select * from TestTable1 where Stu_Status = 0) --检索表1中新增数据,默认新增状态为0
begin
select * into #temp from TestTable1 where Stu_Status = 0 --将新增数据写如一临时表中
--select * from #temp --查看临时表数据
insert into pubs.dbo.TestTable2 select Stu_Name,Stu_Sex,Stu_Age,Stu_Status from #temp --将临时表数据写如表2中
--select * from pubs.dbo.TestTable2 --查看表2数据
update TestTable1 set Stu_Status = 1 where Stu_ID in (select Stu_ID from #temp)--更新表1中数据status=1
--select * from TestTable1--查看表1数据
--update TestTable1 set Stu_Status = 0 where Stu_ID in (select Stu_ID from TestTable1)--将表1数据更改为初始值status=0
drop table #temp --删除临时表
end
else
select * from pubs.dbo.TestTable2
commit transaction
return 1
rollback transaction
return 2
GO