请看一下我的存储过程
create proc tq_yg_tqxs(@id_date datetime,@oc_char varchar(255) out)
AS
/*
生成员工 台区供电量，售电量，台区线损率
*/
//
declare @sys_maxid int,@sys_num int
declare @ll_col int
//
-----------------------------------------------
declare @ls_gh varchar(20),@ls_ssbz varchar(20)
declare @ls_ssbm varchar(20),@ls_gwmc varchar(20)
declare @ls_dybz varchar(20)
-----------------------------------------------
declare @ls_tqh varchar(20),@ls_bz varchar(20)
declare @li_pos int
-----------------------------------------------
DECLARE cby_cursor CURSOR FOR
SELECT gh,ssbz,ssbm,gwmc,dybz
FROM ji_ry
ORDER BY gh
IF @@ERROR <> 0
BEGIN
ROLLBACK WORK
RETURN
END
begin tran
delete from tmp_ygtq where sjrq = @id_date
IF @@ERROR <> 0
BEGIN
ROLLBACK WORK
RETURN
END
begin tran
insert tmp_ygtq
select * from tmp_cbytq
where tmp_cbytq.sjrq = @id_date
IF @@ERROR <> 0
BEGIN
ROLLBACK WORK
RETURN
END
OPEN cby_cursor
FETCH NEXT FROM cby_cursor
INTO @ls_gh,@ls_ssbz,@ls_ssbm,@ls_gwmc,@ls_dybz
WHILE @@FETCH_STATUS = 0
BEGIN
if (charindex('抄表员',@ls_gwmc,0) > 0) or (@ls_gwmc is null) or (@ls_gwmc = '')
begin
FETCH NEXT FROM cby_cursor
INTO @ls_gh,@ls_ssbz,@ls_ssbm,@ls_gwmc,@ls_dybz
continue
end
/抄表班长台区线损 开始/
if (charindex('班长',@ls_gwmc,0) > 0 and charindex('抄表',@ls_ssbm,0) > 0) or
(charindex('装表员',@ls_gwmc,0)>0 and charindex('装表',@ls_ssbm,0) > 0)
begin
begin tran
insert tmp_ygtq
select distinct @ls_gh,tqh,@id_date,0,0,0,0,0,0,0,0,0,0,0 from di_cbyjj where sjrq = @id_date and gh in (select gh from ji_ry where ssbz = @ls_dybz)
order by tqh
IF @@ERROR <> 0
BEGIN
ROLLBACK WORK
RETURN
END
--员工台区售电量
begin tran
update tmp_ygtq
set tmp_ygtq.y_tqsdl = (select sum(isnull(di_cbyjj.cjdl,0)) from di_cbyjj where di_cbyjj.sjrq = @id_date and di_cbyjj.tqh = tmp_ygtq.tqh and di_cbyjj.gh in (select ji_ry.gh from ji_ry where ji_ry.ssbz = @ls_dybz))
where tmp_ygtq.gh = @ls_gh and sjrq = @id_date
IF @@ERROR <> 0
BEGIN
ROLLBACK WORK
RETURN
END
FETCH NEXT FROM cby_cursor
INTO @ls_gh,@ls_ssbz,@ls_ssbm,@ls_gwmc,@ls_dybz
continue
end
/抄表班长台区线损 结束/
/装表内勤台区线损 开始/
if charindex('装表',@ls_ssbm,0) > 0 and charindex('内勤',@ls_gwmc,0) > 0
begin
set @li_pos = charindex('+',@ls_dybz,0)
while not @li_pos = 0
begin
set @ls_bz = left(ltrim(rtrim(@ls_dybz)),@li_pos - 1)
begin tran
insert tmp_ygtq
select distinct @ls_gh,tqh,@id_date,0,0,0,0,0,0,0,0,0,0,0 from di_cbyjj where sjrq = @id_date and gh in (select gh from ji_ry where ssbz = @ls_bz)
order by tqh
IF @@ERROR <> 0
BEGIN
ROLLBACK WORK
RETURN
END
--员工台区售电量
begin tran
update tmp_ygtq
set tmp_ygtq.y_tqsdl =tmp_ygtq.y_tqsdl + (select sum(isnull(di_cbyjj.cjdl,0)) from di_cbyjj where di_cbyjj.sjrq = @id_date and di_cbyjj.tqh = tmp_ygtq.tqh and di_cbyjj.gh in (select ji_ry.gh from ji_ry where ji_ry.ssbz = @ls_bz))
where tmp_ygtq.gh = @ls_gh and sjrq = @id_date
IF @@ERROR <> 0
BEGIN
ROLLBACK WORK
RETURN
END
set @ls_dybz = right(ltrim(rtrim(@ls_dybz)),len(@ls_dybz) - @li_pos)
set @li_pos = charindex('+',@ls_dybz,0)
end
---------------------------------------------------------------
begin tran
set nocount on
if IF @@ERROR <> 0 goto QuitWithRollback
....
if IF @@ERROR <> 0 goto QuitWithRollback
...
if IF @@ERROR <> 0 goto QuitWithRollback
commit transaction
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
set nocount off
---------------------------------------------------------------
begin TRANSACTION
set nocount on
if IF @@ERROR <> 0 goto QuitWithRollback
....
if IF @@ERROR <> 0 goto QuitWithRollback
...
if IF @@ERROR <> 0 goto QuitWithRollback
commit transaction
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
set nocount off