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