create procedure p
@field1 int,
@field2 int
as
set xact_abort on
begin tran
update demo
set field1=1001
where field1=@field1select distinct * into # from demo
truncate table demo
insert demo select * from #
drop table #commit tran
@field1 int,
@field2 int
as
set xact_abort on
begin tran
update demo
set field1=1001
where field1=@field1select distinct * into # from demo
truncate table demo
insert demo select * from #
drop table #commit tran
@field1 int,
@field2 int
as
set xact_abort on
begin tran
update demo
set field1=@field2
where field1=@field1select distinct * into # from demo
truncate table demo
insert demo select * from #
drop table #commit tran
as beging
insert demo select @field1,field2 from demo where field1=@field2 and field2 not in(select field2 from demo where field1=@field1)
delete demo where field1=@field2
endexec ptest('1002','1001')
create table demo(field1 int,field2 int)
insert demo select 1001 , 1002
insert demo select 1001 , 1003
insert demo select 1001 , 1001
insert demo select 1002 , 1004
insert demo select 1002 , 1001
insert demo select 1002 , 1003
go
--创建过程
create proc ptest(@field2 int,@field1 int)
as begin
set nocount on
begin tran
insert demo select @field1,field2 from demo where field1=@field2 and field2 not in(select field2 from demo where field1=@field1)
delete demo where field1=@field2
if @@error<>0 begin
rollback tran
end
commit tran
select * from demo
set nocount off
end
go--执行
exec ptest 1002,1001--结果
field1 field2
----------- -----------
1001 1002
1001 1003
1001 1001
1001 1004
--删除测试环境
drop table demo
go
drop proc ptest
go