SQL Server 2008 存储过程+事务+异常捕获+输出参数

带异常输出参数

如果要在最后再提交事务，那么 TRANSACTION 后面必须加 RETURN，否则脚本会报：COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION 的错

/*

declare @mesg nvarchar(max)      
EXEC ZL_AutoCertificate_Build 22922,@mesg OUTPUT
SELECT @mesg

*/

/****** Object:  StoredProcedure [dbo].[ZL_AutoCertificate_Build]    Script Date: 09/28/2018 10:04:08 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZL_AutoCertificate_Build]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ZL_AutoCertificate_Build]
GO

/****** Object:  StoredProcedure [dbo].[ZL_AutoCertificate_Build]    Script Date: 09/28/2018 10:04:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[ZL_AutoCertificate_Build](    
@ProduceTaskID INT,
@mesg nvarchar(MAX) OUTPUT        
)      
AS    
  
BEGIN  
    SET @mesg=''
    
    DECLARE @ZL_ConstructionName NVARCHAR(200) 
    DECLARE @ZL_CustomerName NVARCHAR(200) 
    DECLARE @CementGradeID INT 
    DECLARE @BeginTime DATETIME 
    DECLARE @SumCube DECIMAL(18,2)
    
    BEGIN TRANSACTION
    
    SELECT @ZL_ConstructionName=r.ZL_GongCheng,@ZL_CustomerName=r.ZL_WeituoDanWei,
    @CementGradeID=(SELECT TOP 1 CementGradeID FROM Biz_Sell_CementGrade WHERE Name=r.ZL_QiangDu),
    @BeginTime=r.ZL_YuanCaiQianFaRiQi ,@SumCube=r.ZL_FangLiang
    FROM ZL_RenWu AS r
    WHERE r.ProduceTaskID=@ProduceTaskID
    
    BEGIN TRY 
    
    INSERT INTO dbo.Testing_Certificate ([No],ZL_ConstructionName, ZL_CustomerName,
                ZL_RenWuID, CementGradeID, BeginTime, EndTime, SumCube, UserID,
                UserName, Createtime)
                VALUES((SELECT dbo.NO_Testing_CertificateNO()),@ZL_ConstructionName,@ZL_CustomerName,@ProduceTaskID,
                @CementGradeID,@BeginTime,GETDATE(),@SumCube,52,'Admin',GETDATE())
    DECLARE @CertificateID INT   
    SET @CertificateID=@@IDENTITY;  
    
    DECLARE @BriquetteEntrustItemID INT 
    DECLARE @BriquetteEntrustItemNo NVARCHAR(MAX) 
    DECLARE @ConcreteInspectionTypeID INT
    
    DECLARE cur_cer CURSOR DYNAMIC FOR 
    SELECT BriquetteEntrustItemID,b.[No]+'-'+bi.TestPieceNO,b.ConcreteInspectionTypeID
      FROM Biz_Tech_BriquetteEntrustItem bi           
    INNER JOIN Biz_Tech_BriquetteEntrust AS b ON b.BriquetteEntrustID = bi.BriquetteEntrustID          
    WHERE b.ProduceTaskID=@ProduceTaskID
    OPEN cur_cer 
    FETCH NEXT FROM cur_cer INTO @BriquetteEntrustItemID,@BriquetteEntrustItemNo,@ConcreteInspectionTypeID
    WHILE(@@FETCH_STATUS=0)
    BEGIN
        IF(@ConcreteInspectionTypeID=1)
        BEGIN
            DECLARE @ConcreteInspectionID_TKY INT 
            IF NOT EXISTS(SELECT 1 FROM Testing_ConcreteInspectionItem_TKY WHERE BriquetteEntrustItemID=@BriquetteEntrustItemID)
            BEGIN
                SET @mesg=@mesg+@BriquetteEntrustItemNo+','
            END
            ELSE
            BEGIN
                SELECT @ConcreteInspectionID_TKY=ConcreteInspectionID FROM Testing_ConcreteInspectionItem_TKY WHERE BriquetteEntrustItemID=@BriquetteEntrustItemID
                INSERT INTO dbo.Testing_CertificateOfConcrete (CertificateID,ConcreteInspectionID) VALUES(@CertificateID,@ConcreteInspectionID_TKY)
            END            
        END

        IF(@ConcreteInspectionTypeID=2)
        BEGIN
            DECLARE @ConcreteInspectionID_TKS INT 
            IF NOT EXISTS(SELECT 1 FROM Testing_ConcreteInspectionItem_TKS WHERE BriquetteEntrustItemID=@BriquetteEntrustItemID)
            BEGIN
                SET @mesg=@mesg+@BriquetteEntrustItemNo+','
            END
            ELSE
            BEGIN
                SELECT @ConcreteInspectionID_TKS=ConcreteInspectionID FROM Testing_ConcreteInspectionItem_TKS WHERE BriquetteEntrustItemID=@BriquetteEntrustItemID
                INSERT INTO dbo.Testing_CertificateOfConcrete (CertificateID,ConcreteInspectionID) VALUES(@CertificateID,@ConcreteInspectionID_TKS)
            END
        END        
        
        FETCH NEXT FROM cur_cer INTO @BriquetteEntrustItemID,@BriquetteEntrustItemNo,@ConcreteInspectionTypeID
    END
    CLOSE cur_cer 
    DEALLOCATE cur_cer
    
    IF(@mesg<>'')
    BEGIN
        SET @mesg='检验委托没有做实验,编号:'+@mesg
        ROLLBACK TRANSACTION
        RETURN 
    END
    
    END TRY
    BEGIN CATCH
        SET @mesg=ERROR_MESSAGE()
        ROLLBACK TRANSACTION
        RETURN 
    END CATCH

    COMMIT TRANSACTION
    
END   

GO