sqlServer显示事务和异常的处理

1、
Begin Transaction [refreshtb]
Begin Try
...
COMMIT TRANSACTION [refreshtb]
END TRY

BEGIN CATCH
   ROLLBACK TRANSACTION [refreshtb]
END CATCH

2、
USE AdventureWorks2022;  
GO  
IF EXISTS (SELECT name FROM sys.objects  
           WHERE name = N'SaveTranExample')  
    DROP PROCEDURE SaveTranExample;  
GO  
CREATE PROCEDURE SaveTranExample  
    @InputCandidateID INT  
AS  
    -- Detect whether the procedure was called  
    -- from an active transaction and save  
    -- that for later use.  
    -- In the procedure, @TranCounter = 0  
    -- means there was no active transaction  
    -- and the procedure started one.  
    -- @TranCounter > 0 means an active  
    -- transaction was started before the   
    -- procedure was called.  
    DECLARE @TranCounter INT;  
    SET @TranCounter = @@TRANCOUNT;  
    IF @TranCounter > 0  
        -- Procedure called when there is  
        -- an active transaction.  
        -- Create a savepoint to be able  
        -- to roll back only the work done  
        -- in the procedure if there is an  
        -- error.  
        SAVE TRANSACTION ProcedureSave;  
    ELSE  
        -- Procedure must start its own  
        -- transaction.  
        BEGIN TRANSACTION;  
    -- Modify database.  
    BEGIN TRY  
        DELETE HumanResources.JobCandidate  
            WHERE JobCandidateID = @InputCandidateID;  
        -- Get here if no errors; must commit  
        -- any transaction started in the  
        -- procedure, but not commit a transaction  
        -- started before the transaction was called.  
        IF @TranCounter = 0  
            -- @TranCounter = 0 means no transaction was  
            -- started before the procedure was called.  
            -- The procedure must commit the transaction  
            -- it started.  
            COMMIT TRANSACTION;  
    END TRY  
    BEGIN CATCH  
        -- An error occurred; must determine  
        -- which type of rollback will roll  
        -- back only the work done in the  
        -- procedure.  
        IF @TranCounter = 0  
            -- Transaction started in procedure.  
            -- Roll back complete transaction.  
            ROLLBACK TRANSACTION;  
        ELSE  
            -- Transaction started before procedure  
            -- called, do not roll back modifications  
            -- made before the procedure was called.  
            IF XACT_STATE() <> -1  
                -- If the transaction is still valid, just  
                -- roll back to the savepoint set at the  
                -- start of the stored procedure.  
                ROLLBACK TRANSACTION ProcedureSave;  
                -- If the transaction is uncommitable, a  
                -- rollback to the savepoint is not allowed  
                -- because the savepoint rollback writes to  
                -- the log. Just return to the caller, which  
                -- should roll back the outer transaction.  
  
        -- After the appropriate rollback, echo error  
        -- information to the caller.  
        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  
  
        SELECT @ErrorMessage = ERROR_MESSAGE();  
        SELECT @ErrorSeverity = ERROR_SEVERITY();  
        SELECT @ErrorState = ERROR_STATE();  
  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );  
    END CATCH  
GO
参考链接：https://learn.microsoft.com/zh-cn/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-ver16

ALTER  PROCEDURE [dbo].[sp_name]

AS    
BEGIN    
SET NOCOUNT ON;    

...

END

 BEGIN TRY   

...

 END TRY 
 BEGIN CATCH
SET @Message = 'failed with error.'    
       + ' ErrorProcedure: ' + ERROR_PROCEDURE()     
       + ', ErrorMessage: ' + ERROR_MESSAGE()    
       + ', ErrorNumber: ' + CONVERT(NVARCHAR, ERROR_NUMBER())    
       + ', ErrorState: ' + CONVERT(NVARCHAR, +ERROR_STATE())     
       + ', ErrorSeverity: ' + CONVERT(NVARCHAR, +ERROR_SEVERITY())     
       + ', ErrorLine: ' + CONVERT(NVARCHAR, +ERROR_LINE())    
  PRINT @Message

  EXEC @return_value = [dbo].[proc_Log_table]    
    @MonJobOrder_Name = '@Task_Name',
    @ParsOrMessage = @Message,
    @ParentProcID = @ProcID,
    @ProcVer = @ProcVersion,
    @severity = N'ERROR';
  THROW;
 END CATCH