SQL Add transaction Processing

Tag: date () Exist Port OCA tab request Item Transaction Sele

--modi by LMT
Declare
@errorSum INT--record number of errors
Begin
Create table #CheckreqAccState (checkreqid varchar, advid varchar), docadvstatus int, accstate varchar (20))
Set @SqlTxt = ' INSERT into #CheckreqAccState (checkreqid, Advid, Docadvstatus, accstate)
Select A.checkreqid, A.advid, B.docadvstatus, accstate from Ghmtdb. Tcheckreq_main (NOLOCK) A left join GHMTDB.dbo.tRecipe_Detail (NOLOCK) B on a.advid = B.advid
where a.checkreqid in (' + @CheckReqID + ') '
EXEC (@SqlTxt)
if exists (SELECT * from #CheckreqAccState where Docadvstatus < 3)
Begin
Set @ReqInfor = ' true fee failure: ' ' + @CheckReqID + ' in the application number, please verify that there are no charges recorded.
drop table #CheckreqAccState
Goto ERR
End
Else
BEGIN TRANSACTION
Update Ghmtdb. Tcheckreq_main
Set state = 3, Reportuser = @OperUserID, reportdate = GetDate ()
Where checkreqid in (select Checkreqid from #CheckreqAccState)
Set @[email protected] + @ @error
Update Trecipe_detail
Set Performuserid = @OperUserID, Docadvstatus = 5,
Performdate = GETDATE ()
where Advid in (select Advid from #CheckreqAccState)
Set @[email Protected][email protected] @error
Update Trecipe_detail_feeitem
Set Performuserid = @OperUserID, Docadvstatus = 5,
Performdate = GETDATE ()
where Advid in (select Advid from #CheckreqAccState)
Set @[email Protected][email protected] @error
Update Tchargedetail
Set Performuserid = @OperUserID, Docadvstatus = 5,
Performdate = GETDATE ()
where Advid in (select Advid from #CheckreqAccState)
Set @[email Protected][email protected] @error
drop table #CheckreqAccState
If @errorSum > 0
Begin
Set @ReqInfor = ' execution failed '
Select @ReqInfor ' reqinfor ', @ChargeSN ' CHARGESN '
ROLLBACK TRANSACTION
End
Else
Begin
Set @ReqInfor = ' Execute complete '
Select @ReqInfor ' reqinfor ', @ChargeSN ' CHARGESN '
Commit TRANSACTION
End
End
End--end Else if @DataSource = ' outpatient '
End--modi End

SQL Add transaction Processing