存储过程嵌套事务处理

最新推荐文章于 2022-06-15 10:26:57 发布

qizhanfeng

最新推荐文章于 2022-06-15 10:26:57 发布

阅读量203

点赞数

存储过程嵌套事务处理

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

------------------------------------
--用途：次数项目续费
--创建人名称：qzf
--时间：2012-12-21 10:04:00
------------------------------------
ALTER PROCEDURE sp_Card_Cs_XuFei( @msg varchar(200) = '' out , @card_no varchar(50), @content varchar(8000), @gongsi_no varchar(30), @czy_no varchar(30), @czy_name varchar(30) ) AS declare @outer_tran int declare @rst int declare @member_name varchar(200) declare @gongsi_name varchar(200) declare @card_kind_no varchar(200) declare @card_kind_name varchar(200) declare @create_gongsi_no varchar(200) declare @create_gongsi_name varchar(200) declare @jiesuan_fangshi_name varchar(30) declare @id numeric(24,0)

Begin set @outer_tran = @@trancount if (@outer_tran = 0) --单独调用 begin begin tran end else begin --嵌套调用 save tran tranPoint end --信息 select @card_kind_no = card_kind_no,@member_name=member_name, @create_gongsi_no = gongsi_no from card a where card_no = @card_no select @create_gongsi_name = gongsi_name from base_gongsi where <a href="mailto:gongsi_no=@create_gongsi_no">gongsi_no=@create_gongsi_no</a> select @gongsi_name=gongsi_name from base_gongsi where gongsi_no = @gongsi_no select @card_kind_name = card_kind_name from card_kind where <a href="mailto:card_kind_no=@card_kind_no">card_kind_no=@card_kind_no</a> select @jiesuan_fangshi_name = jiesuan_fangshi_name from info_jiesuanfangshi where jiesuan_fangshi_no='J001' --插入次数项目明细 insert into Card_Detail_Cs( card_no, gongsi_no, gongsi_name, create_gongsi_no, create_gongsi_name, riqi, member_name, card_kind_name, czy_no, czy_name, remark, cs_kind, danhao, goods_no, goods_name, goods_je, goods_jfcs, goods_dfcs, goods_yucs) select @card_no,@gongsi_no,@gongsi_name,'','',getdate(), @member_name,@card_kind_name,@czy_no,@czy_name,'',case when col2 > 0 then '手动增加' else '手动减少' end,'' ,col1,b.goods_name,0, case when col2 > 0 then col2 else 0 end,case when col2 > 0 then 0 else (-1)*col2 end,0 from F_GetTable(@content,',','|') a left join goods b on a.col1=b.goods_no if (@@error <> 0) begin set @msg = '插入次数项目明细失败。' goto Err end --更新信息 update a set goods_cs=goods_cs+b.col2,goods_yucs=goods_yucs+b.col2 from card_goods a inner join F_GetTable(@content,',','|') b on a.goods_no=b.col1 where a.card_no = @card_no if (@rst <> 0 or @@error <> 0) begin set @msg = '更新卡内次数项目失败。' goto Err end --插入不存在的次数项目 insert card_goods(card_no, goods_no, goods_cs, goods_usecs, goods_yucs, je, kind, remark_je, remark) select @card_no,col1,col2,0,col2, 0,'',0,'' from F_GetTable(@content,',','|') a where col1 not in (select goods_no from card_goods where card_no = @card_no) if (@rst <> 0 or @@error <> 0) begin set @msg = '插入不存在的次数项目失败。' goto Err end if (@outer_tran = 0) --单独调用 begin commit tran end

return 0

End Err: Begin if (@outer_tran = 0) begin rollback tran end else begin rollback tran tranPoint --嵌套调用回滚到事务点 end return -1 End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO