關於事務的儲存過程

ALTER  PROCEDURE [dbo].[GDS_update]

 

AS

BEGIN

declare @i varchar(10)

select  @i=0

DECLARE @ErrorNBR int

BEGIN TRAN

  drop table dbo.GDS_Rate_Temp 

SELECT @ErrorNBR = @@ERROR

IF @ErrorNBR <> 0

GOTO UndoTran

select BG as [事業群],BU as [事業處],ParentDept as [部門],[GDS_Revenue_Rate_View_bak].* into dbo.GDS_Rate_Temp

from [GDS_Revenue_Rate_View_bak] left join dbo._GDS_table on [損益部門]=DeptName

SELECT @ErrorNBR = @@ERROR

IF @ErrorNBR <> 0

GOTO UndoTran

COMMIT TRAN

select @i=1

UndoTran:

IF @ErrorNBR <> 0

BEGIN

PRINT CAST(@ErrorNBR as varchar(6)) +

' occurred after an attempt to insert into dbo.GDS_Rate_Temp'

ROLLBACK TRAN

END

select @i

END

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7899089/viewspace-610685/，如需轉載，請註明出處，否則將追究法律責任。