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

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

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

转载于:https://www.cnblogs.com/ArtlessBruin/p/7144273.html