SET XACT_ABORT ON
begin transaction
update a set a.spare_amount=a.spare_amount+b.out_amount from a1 a join a2 b on a.inid=b.inid where b.outid='2'
update b set flag=0 from a1 a join a2 b on a.id=b.id where b.outid='2'
commit transaction
SET XACT_ABORT off
begin transaction
update a set a.spare_amount=a.spare_amount+b.out_amount from a1 a join a2 b on a.inid=b.inid where b.outid='2'
update b set flag=0 from a1 a join a2 b on a.id=b.id where b.outid='2'
commit transaction
SET XACT_ABORT off
我想用程序处理,传参数的那种:
加上这个东西的,因为我要在别的程序里调用啊!!!create proc_update_material
(
@outid int
)
as
(
@outid int
)
as
set nocount on
begin transaction
update a set spare_amount=a.spare_amount+b.out_amount
from a1 a join (
select inid,sum(out_amount) as out_amount --考虑a2表的inid可能重复
from a2 where outid=@outid
and flag<>0 --自作主张加的条件,防止一条记录计算多次
) b on a.inid=b.inid
update b set flag=0 from a1 a join a2 b on a.id=b.id where b.outid=@outid
and flag<>0
commit transaction
go
SET XACT_ABORT ON
BEGIN TRAN T1
update a set a.spare_amount=a.spare_amount+b.out_amount from a1 a join a2 b on a.inid=b.inid where b.outid='2'
BEGIN TRAN M2 WITH MARK
update b set flag=0 from a1 a join a2 b on a.id=b.id where b.outid='2'
commit transaction
COMMIT TRAN M2
COMMIT TRAN T1
go
(
@outid int
)
as
set nocount on
begin transaction
update a set spare_amount=a.spare_amount+b.out_amount
from a1 a join (
select inid,sum(out_amount) as out_amount --考虑a2表的inid可能重复
from a2 where outid=@outid
and flag<>0 --自作主张加的条件,防止一条记录计算多次
) b on a.inid=b.inid
if @@error<>0 goto Err_Handle
update b set flag=0 from a1 a join a2 b on a.id=b.id where b.outid=@outid
and flag<>0
if @@error<>0 goto Err_Handle
commit transaction
return 0 -- 0代表成功
Err_Handle:
ROLLBACK TRANSACTION
return 1 -- 1代表失败
go
as
set nocount on
begin transaction
update a set spare_amount=a.spare_amount+b.out_amount
from a1 a join (
select inid,sum(out_amount) as out_amount
from a2 where outid=@outid
group by inid ) b on a.inid=b.inid
if @@error<>0 goto err
update b set b.flag=0
from a1 a join (
select inid,sum(out_amount) as out_amount
from a2 where outid=@outid
group by inid
) b on a.inid=b.inid
if @@error<>0 goto err
commit transaction err:
rollback
go
当 SET XACT_ABORT 为 ON 时,如果 Transact-SQL 语句产生运行时错误,整个事务将终止并回滚。为 OFF 时,只回滚产生错误的 Transact-SQL 语句,而事务将继续进行处理。create proc pUpdate @outid int --@outid 指要處理記錄的outid
as
set nocount on
SET XACT_ABORT ON
begin transaction
update a set spare_amount=a.spare_amount+b.out_amount
from a1 a join (
select inid,sum(out_amount) as out_amount
from a2 where outid=@outid
group by inid ) b on a.inid=b.inid
update b set b.flag=0
from a1 a join (
select inid,sum(out_amount) as out_amount
from a2 where outid=@outid
group by inid
) b on a.inid=b.inid
commit
go
派生表 'b' 不可更新,因为它包含聚合。
--处理的存储过程
create proc p_process
@outid int=2 --这样比较通用一点,你以后可以调用
as
set xact_abort on
begin tran
update A1 set spare_amount=isnull(a.spare_amount,0)+isnull(b.out_amount,0)
from A1 a join(
select inid,out_amount=sum(out_amount)
from A2
where Outid=@outid
group by inid
)b on a.inid=b.inid
update A2 set flag=0
where Outid=@outid
commit tran
go--调用
exec p_process
create table A1(Inid int,spare_amount int,memo varchar(10))
insert A1 select 1,1000,'不错'
union all select 2,2000,'很好'
union all select 3,3000,'海星'
union all select 4,6000,'很差'create table A2(Id int,Outid int,inid int,out_amount int,flag int,memo varchar(10))
insert A2 select 1,2,1,1000,1,'不错'
union all select 2,2,2,200 ,1,'海星'
union all select 3,2,3,100 ,1,'错了'
go--处理的存储过程
create proc p_process
@outid int=2 --这样比较通用一点,你以后可以调用
as
set xact_abort on
begin tran
update A1 set spare_amount=isnull(a.spare_amount,0)+isnull(b.out_amount,0)
from A1 a join(
select inid,out_amount=sum(out_amount)
from A2
where Outid=@outid
group by inid
)b on a.inid=b.inid
update A2 set flag=0
where Outid=@outid
commit tran
go--调用
exec p_process
go--显示处理结果
select * from A1
select * from A2
go--删除测试
drop table A1,A2
drop proc p_process/*--测试结果
Inid spare_amount memo
----------- ------------ ----------
1 2000 不错
2 2200 很好
3 3100 海星
4 6000 很差(所影响的行数为 4 行)Id Outid inid out_amount flag memo
----------- ----------- ----------- ----------- ----------- ----------
1 2 1 1000 0 不错
2 2 2 200 0 海星
3 2 3 100 0 错了(所影响的行数为 3 行)
--*/
@outid int=2 --这样比较通用一点,你以后可以调用
as
set xact_abort on
begin tran
update A1 set spare_amount=isnull(a.spare_amount,0)+isnull(b.out_amount,0)
from A1 a join(
select inid,out_amount=sum(out_amount)
from A2
where Outid=@outid and flag<>0
group by inid
)b on a.inid=b.inid
update A2 set flag=0
where Outid=@outid
commit tran
go
ok了我在程序里循环调用它应该不会出问题吧?帮我解决了两个难题,你如果在北京等我回去请你喝酒!