SqlServer 较可以学习的存储过程 对事务,出现错误的导向

ALTER    PROCEDURE usp_XXXXXX
(@DateFrom VARCHAR(20), @DateTo VARCHAR(20))


AS 


DECLARE @PLTID VARCHAR(20)        --Pallet ID
DECLARE @CTNID VARCHAR(30)        --Carton ID


DECLARE @intError INT
DECLARE @strSql  NVARCHAR(3000)
DECLARE @Cnt INT


SET @CTNPERPLT=100  


SET @Cnt=0


DECLARE PLT_cursor CURSOR SCROLL FOR  
SELECT DISTINCT PalletNo FROM ****  WITH(NOLOCK)  order by palletno
OPEN PLT_cursor
FETCH NEXT FROM PLT_cursor
INTO @PLTID


WHILE @@FETCH_STATUS=0 
BEGIN


    SELECT TOP 1 @SNQTYONPLT=Qty,@PN=PN,@SO=SO,@shipment=shipment,@mfdate=mfdate,@timein=timein FROM ****  WITH(NOLOCK) WHERE ****


    IF LEN(@SNQTYONPLT)=0 OR LEN(@PN)=0 OR LEN(@SO)=0 OR LEN(@shipment)=0 OR LEN(@mfdate)=0 OR LEN(@timein)=0 
BEGIN
RAISERROR ('Some Column is NULL.',16,1)
SET @intError=0
GOTO Err_Handle
END


    BEGIN TRAN
    SET @strSql='INSERT INTO ****'
    SET @strSql=@strSql +'****'


    EXEC @intError=SP_EXECUTESQL @strSql


    IF @intError <> 0
    BEGIN
GoTo Err_Handle
    END




    SET @CTNSEQ=1
    IF UPPER(LEFT(Rtrim(Ltrim(@PN)),2))='9B' 
BEGIN
        DECLARE CTN_cursor CURSOR SCROLL FOR 
                SELECT **** FROM **** WHERE trid IN (****)
        OPEN CTN_cursor
        FETCH NEXT FROM CTN_cursor
                INTO @CurCTNID
               
        WHILE @@FETCH_STATUS=0 


BEGIN
BEGIN TRAN
            SELECT @SNQTYONCTN=count(*) FROM **** WHERE carton=@CurCTNID


    SET @strSql='INSERT INTO ****'
    SET @strSql=@strSql +'****'


EXEC @intError=SP_EXECUTESQL @strSql
  IF @intError <> 0
    BEGIN
   GoTo Err_Handle
    END


SET @CTNSEQ=@CTNSEQ+1
  END
       
  CLOSE SN_cursor 
  DEALLOCATE SN_cursor 
IF @@trancount<>0
BEGIN
            COMMIT TRAN
END
        FETCH NEXT FROM CTN_cursor  
    INTO @CurCTNID  


        END
CLOSE CTN_cursor
DEALLOCATE CTN_cursor
GOTO NewPLT
        END




NewPLT:
UPDATE **** SET **** WHERE ****

    IF @@ERROR<>0   
GOTO Err_Handle
    ELSE
IF @@trancount<>0
BEGIN
COMMIT TRAN
END
 
    FETCH NEXT FROM PLT_cursor  INTO @PLTID
    
    SET @Cnt=@Cnt+1
END


    IF @@ERROR<>0   
GOTO Err_Handle
    ELSE
IF @@trancount<>0
BEGIN
           COMMIT TRAN
END


    CLOSE PLT_cursor 
    DEALLOCATE PLT_cursor 




RETURN 0




Err_Handle:
    IF @@trancount<>0
BEGIN
           ROLLBACK TRAN
END
    RETURN @@ERROR