存儲過程中的事務處理

以下是一個存儲過程范例：

CREATE PROCEDURE dbo.PrSave_Repairing
  @returnMessage nvarchar(50) output,
  @LRR nvarchar(30),
  @SN nvarchar(30),
  @LOCATION nvarchar(50),
  @OPERATION nvarchar(100),
  @DUTY nvarchar(2),
  @RESULT nvarchar(2),
  @REMARKS nvarchar(100),
  @USERID nvarchar(10),
  @STATION nvarchar(20),
  @PCNAME nvarchar(20),
  @ROUTIN nvarchar(20),
  @FAILCODE nvarchar(20)
 
AS

 DECLARE @PREV nvarchar (20)
 DECLARE @CURR nvarchar (20)
 DECLARE @POST nvarchar (20)
 DECLARE @PNEXT nvarchar (20)
 DECLARE @ISBGA bit

   BEGIN
       SELECT @PREV=PREV,@CURR=CURR,@POST=POST FROM MASTER WITH(NOLOCK) WHERE SN=@SN AND ISEND=0
      
       IF (@RESULT = '02')--BGA不良
           BEGIN
             SET @ISBGA=1 --未結束
             SELECT @POST=NODECODE,@PNEXT=POSTNODE FROM ROUTIN WITH(NOLOCK) WHERE PREVNODE=@CURR
           END
       ELSE
           BEGIN
              SET @ISBGA=0 --結束
              SELECT @PNEXT=POSTNODE FROM ROUTIN WITH(NOLOCK) WHERE NODECODE=@POST
           END

       BEGIN TRAN              
           UPDATE MASTER WITH(ROWLOCK) SET PREV=@STATION,CURR=@POST,POST=@PNEXT,ISBGA=@ISBGA,RESULT=@RESULT WHERE SN=@SN
           IF @@ERROR <> 0
                 BEGIN
                      --SET @returnMessage = 'SN: '+@SN + ' -->記錄保存失敗！'
                      ROLLBACK TRAN
                      RETURN -1                          
                 END
           INSERT INTO HOSTORY WITH(ROWLOCK)(LRR,CURRCODE,CURRDATE,STATION,LOCATION,ROUTIN,SN)
                     VALUES(@LRR,@USERID,GETDATE(),@STATION,@PCNAME,@ROUTIN,@SN)
           IF @@ERROR <> 0
                 BEGIN
                      --SET @returnMessage = 'SN: '+@SN + ' -->記錄保存失敗！'
                      ROLLBACK TRAN
                      RETURN -1
                 END
           INSERT INTO REP01 WITH(ROWLOCK)(LRR,SN,LOCATION,OPERATION,DUTY,RESULT,REMARKS,REPCODE,REPDATE,FAILCODE)
                                       VALUES(@LRR,@SN,@LOCATION,@OPERATION,@DUTY,@RESULT,@REMARKS,@USERID,GETDATE(),@FAILCODE)
           IF @@ERROR <> 0
                 BEGIN
                      --SET @returnMessage = 'SN: '+@SN + ' -->記錄保存失敗！'
                      ROLLBACK TRAN
                      RETURN -1
                 END
  
         COMMIT TRAN
         --SET @returnMessage = 'SN: '+@SN + ' -->記錄保存成功！'
         RETURN 0
   END
GO