存儲過程中的事務處理

来源：互联网发布：58淘宝模特兼职可信吗编辑：程序博客网时间：2024/05/18 01:19

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

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