我有一个带有嵌套的相当复杂的SP（逻辑上），IF BEGIN END ELSE该嵌套逻辑中有多个端点，其中逻辑失败，而我RAISERROR和两个地方导致成功和SQL被执行。

如何在SP的末尾捕获错误并执行 IF Error Count>0 THEN ROLLBACK

DECLARE @errCount int=0
DECLARE @DataSetCount int=0
DECLARE @countCurrent int=0
DECLARE @countHistorical int=0


IF (isnumeric(@DataSetID)=(0) OR @DataSetID=(0)) 
BEGIN
    RAISERROR('The DataSet specfied does not appear to be valid', 5, 1)
END
ELSE
IF  (@Destination='C' OR @Destination='H') 
    BEGIN
        if Exists (SELECT NULL from tblOpportunityDataSets where DataSetID=@DataSetID)
        BEGIN
            SET @countCurrent=(SELECT COUNT(1) from tblOptyRecordsCurrent where DataSetID=@DataSetID)
            SET @countHistorical=(SELECT COUNT(1) from tblOptyRecordsHistorical where DataSetID=@DataSetID)
            IF @destination='C'
            BEGIN
                if @countCurrent>0 
                BEGIN
                    RAISERROR('There are already existing records in the Current Tables for the specified DataSet', 5, 1)
                END ELSE
                if @countHistorical=0 
                BEGIN
                    RAISERROR('There do not appear to be any records in the Historical Tables to transfer for the specified Dataset', 5, 1)
                END ELSE
                BEGIN
                    -- ENTER TRANSFER CODE
                    INSERT INTO tblRecordsHistorical
                    ( X, Y, Z ) 
                    SELECT X, Y, Z  FROM tblA
                    WHERE x=y
                    -- Check that record count in both tables match
                    SET @countCurrent=(SELECT COUNT(1) from tblOptyRecordsCurrent where DataSetID=@DataSetID)
                    SET @countHistorical=(SELECT COUNT(1) from tblOptyRecordsHistorical where DataSetID=@DataSetID)
                    IF (@countCurrent<>@countHistorical)
                    BEGIN
                        RAISERROR('There was an error whilst copying the records into the Historical Tables, Source and Destination Record Count do not match', 5, 1)
                    END ELSE
                    BEGIN


                    END
                END
            END ELSE
            IF @Destination='H'
            BEGIN
                if @countHistorical>0 
                BEGIN
                    RAISERROR('There are already existing records in the Historical Tables for the specified DataSet', 5, 1)
                END ELSE
                if @countCurrent=0 
                BEGIN
                    RAISERROR('There do not appear to be any records in the Historical Tables to transfer for the specified Dataset', 5, 1)
                END ELSE
                BEGIN
                    RAISERROR('DataSet Found, ready to transfer records to HISTORICAL', 5, 1)       
                    -- ENTER TRANSFER CODE
                    INSERT INTO tblOptyRecordsCurrent
                    ( X, Y, Z ) 
                    SELECT X, Y, Z  FROM tblB
                    WHERE x=y
                    -- Check that record count in both tables match
                    SET @countCurrent=(SELECT COUNT(1) from tblOptyRecordsCurrent where DataSetID=@DataSetID)
                    SET @countHistorical=(SELECT COUNT(1) from tblOptyRecordsHistorical where DataSetID=@DataSetID)



                END
            END
        END
        ELSE
        BEGIN
            RAISERROR('The DataSet you have specified cannot be found', 5, 1)
        END
    END
    ELSE
    BEGIN
        RAISERROR('You have not specified a valid Destination', 5, 1)
    END

请注意，在包含INSERT INTO代码的两部分中，将至少有两个附加的SQL Action语句，它们必须全部工作或全部失败。

编辑：我已经与

BEGIN TRAN
BEGIN TRY
    -- STATEMENT 1
    INSERT INTO X WHERE Y
    -- STATEMENT 2
    DELETE FROM X WHERE Y
    -- STATEMENT 3
    UPDATE X WHERE Y
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    RAISERROR('There was an error whilst copying the records into the Current Tables.  The Transaction has been rolled back', 5, 1)
END CATCH