• 带有事务和错误处理的存储过程


    以前在开发中需要用到带错误处理的存储过程,在网上找到了解决方案,现在整理在这,以备日后所需,时间长了原文已经找不到了,感谢为我提供帮助的兄弟。

    1.创建错误日志表

    CREATE TABLE [dbo].[t_ErrorLog](
        [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
    	[ErrorTime] [datetime] NOT NULL,
    	[UserName] [sysname] NOT NULL,
    	[ErrorNumber] [int] NOT NULL,
    	[ErrorSeverity] [int] NULL,
    	[ErrorState] [int] NULL,
    	[ErrorProcedure] [nvarchar](126) NULL,
    	[ErrorLine] [int] NULL,
    	[ErrorMessage] [nvarchar](4000) NOT NULL,
     CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED 
    (
    	[ErrorLogID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    ALTER TABLE [dbo].[t_ErrorLog] ADD  CONSTRAINT [DF_ErrorLog_ErrorTime]  DEFAULT (getdate()) FOR [ErrorTime]
    GO
    

    2.创建写入错误日志的存储过程

    CREATE PROCEDURE [dbo].[Error_LogError]
        @ErrorLogID [int] = 0 OUTPUT -- [t_ErrorLog]ID
    AS                               
    BEGIN
        SET NOCOUNT ON;
        -- //错误信息ID
        SET @ErrorLogID = 0;
        BEGIN TRY
            -- //判断有没有错误信息
            IF ERROR_NUMBER() IS NULL
                RETURN;
            -- //Return if inside an uncommittable transaction.
            -- //Data insertion/modification is not allowed when 
            -- //a transaction is in an uncommittable state.
            IF XACT_STATE() = -1
            BEGIN
                PRINT '因为当前事务处于不可提交状态所以不能记录错误信息。 ' 
                    + '为了能够成功记录错误信息,需要在执行Error_LogError前回滚事务。';
                RETURN;
            END
            INSERT [dbo].[t_ErrorLog] 
                (
                [UserName], 
                [ErrorNumber], 
                [ErrorSeverity], 
                [ErrorState], 
                [ErrorProcedure], 
                [ErrorLine], 
                [ErrorMessage]
                ) 
            VALUES 
                (
                CONVERT(sysname, CURRENT_USER), 
                ERROR_NUMBER(),
                ERROR_SEVERITY(),
                ERROR_STATE(),
                ERROR_PROCEDURE(),
                ERROR_LINE(),
                ERROR_MESSAGE()
                );
            SET @ErrorLogID = @@IDENTITY;
        END TRY
        BEGIN CATCH
            EXECUTE Error_PrintError;--//打印错误信息的存储过程
            RETURN -1;
        END CATCH
    END
    

    3.创建打印错误信息的存储过程

    CREATE PROCEDURE [dbo].[Error_PrintError]
    AS
    BEGIN
        SET NOCOUNT ON;
        -- Print error information. 
        PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
              ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
              ', State ' + CONVERT(varchar(5), ERROR_STATE()) + 
              ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + 
              ', Line ' + CONVERT(varchar(5), ERROR_LINE());
        PRINT ERROR_MESSAGE();
    END
    

    4.创建自己的存储过程

    CREATE PROCEDURE PROCEDURE_NAME
    AS
    BEGIN
    SET NOCOUNT ON;
    BEGIN TRY--//开始捕捉异常
        BEGIN TRAN--//开始事务
        --//你的方法
        COMMIT TRAN --//提交事务
    END TRY--//结束捕捉异常
    BEGIN CATCH--//有异常被捕获
        IF @@TRANCOUNT > 0--//判断有没有事务
        BEGIN
            ROLLBACK TRAN--//回滚事务
        END 
        DECLARE @ErrorLogID INT ;
        EXEC Error_LogError @ErrorLogID OUTPUT;--//执行存储过程将错误信息记录在表当中
    END CATCH--//结束异常处理
    END
    
  • 相关阅读:
    JavaScript的valueOf和toString
    Max SPA
    SQL Server从BAK文件还原新的数据库
    SQL Server删除log文件
    为什么要监控sql语句?如何监控?
    Mac下使用zsh不执行/etc/profile文件
    MAC下快速打开指定目录(转)
    干货满满!10分钟看懂Docker和K8S(转)
    Spring Boot Metrics监控之Prometheus&Grafana(转)
    Spring Boot Actuator:健康检查、审计、统计和监控(转)
  • 原文地址:https://www.cnblogs.com/ArtlessBruin/p/7144273.html
Copyright © 2020-2023  润新知