- 目录
shanzm-2020年5月13日
0. 背景说明
之前极其的抱怨使用存储过程，觉得存储过程不应该出现在现在的新项目中，
但是最近研究存储过程，发现存储过程的优点也是及其的耀眼！
之前只盯着存储过程的缺点，有点一叶障目了。
前一周自己摸索着写的存储过程，写的太幼稚了，不规范。
我们在存储过程中的事务中定义了一个临时变量@sum,在事务的每一句sql语句后都@sum+@@error,最后根据@sum是否为0来判断是否有异常，
如果没有异常则@@error为0，有异常则@@error值为错误代码，即一定不为0
所以，可以通过最终的@sum判断是否有异常，
但是有一点要说明的是，@@error对那种重大错误无法捕捉，而且@@error只对其前一句sql语句生效
所以，建议还是使用TRY……CATCH
这里定义一个捕获异常的存储过程，实现将存储过程中出现的异常记录在数据库的异常信息表中。
本示例中，全部的存储过程都是在一个新建的测试数据库ShanTest数据库中进行的
1. 建立异常信息表ErrorLog
USE [ShanTest]
GO
/****** Object: Table [dbo].[ErrorLog] Script Date: 2020-05-11 14:49:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ErrorLog](
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,--异常表ID
[ErrorTime] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_ErrorTime] DEFAULT (getdate()),--异常时间，提供默认值就是当前时间
[UserName] [sysname] NOT NULL, --异常用户名，这里就是dbo,dbo是每个数据库的默认用户，具有所有者权限，全称：datebaseOwner
[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
异常信息表中字段如下：
2. 建立保存异常信息的存储过程
USE [ShanTest]
GO
/****** Object: StoredProcedure [dbo].[pro_ErrorLog] Script Date: 2020-05-11 14:15:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================
-- Author: shanzm
-- Create date: 2020年5月11日
-- Description: 保存存储过程中捕获的异常到ErrorLog表
-- =================================================
CREATE PROCEDURE [dbo].[pro_ErrorLog]
@ErrorLogID [int] = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[ErrorLog]
(
[UserName],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[ErrorProcedure],
[ErrorLine],
[ErrorMessage]
)
VALUES
(
CONVERT(sysname, CURRENT_USER),--current_user ,这里值是dbo,dbo是每个数据库的默认用户，具有所有者权限
--sysname类型 用于表列、变量以及用于存储对象名的存储过程参数，等价与nvachart(120)
ERROR_NUMBER(), --错误代号，有很多错误代号，可以自行百度
ERROR_SEVERITY(), --错误的严重性
ERROR_STATE(), --错误的状态码
ERROR_PROCEDURE(), --错误的存储过程
ERROR_LINE(), --错误行号
ERROR_MESSAGE() --错误信息
);
SET @ErrorLogID = @@IDENTITY;--@@IDENTITY 是插入记录时自动产生的ID
execute dbo.pro_PrintError;--改存储过程会将ERROR_MESSAGE()在sql server信息窗口打印出来
END;
3. 建立在SQL Server中打印异常信息的存储过程
在存储过程 pro_ErrorLog 中存储异常信息后，在调用这个存储过程
USE [ShanTest]
GO
/****** Object: StoredProcedure [dbo].[pro_PrintError] Script Date: 2020-05-11 14:43:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: shanzm
-- Create date: 2020年5月11日
-- Description: 在消息框中打印异常信息
-- =============================================
CREATE PROCEDURE [dbo].[pro_PrintError]
AS
BEGIN
SET NOCOUNT ON;
-- Print error information.
PRINT 'ErrorNumber ： ' +CONVERT(varchar(50), ERROR_NUMBER())
PRINT 'ErrorSeverity ： ' + CONVERT(varchar(5), ERROR_SEVERITY())
PRINT 'ErrorState ：' + CONVERT(varchar(5), ERROR_STATE())
PRINT 'ErrorProcedure ：' + ISNULL(ERROR_PROCEDURE(), '-')
PRINT 'ErrorLine ：' + CONVERT(varchar(5), ERROR_LINE());
PRINT 'ErrorMessage ：' + ERROR_MESSAGE();
END;
4. 建立一个用于测试的存储过程抛出异常进行测试
切记我们在业务中需要使用存储过程的时候，一旦使用了事务，则我们必须在BEGIN CATCH语句中判断是否有异常抛出，一旦有异常抛出，则存储过程中的事务一定要进行ROLLBACK
USE [ShanTest]
GO
/****** Object: StoredProcedure [dbo].[TestErrorLog] Script Date: 2020-05-11 15:14:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: shanzm
-- Create date: 2020年5月11日
-- Description: 用于抛出异常测试ErrorLog是否可用
-- =============================================
ALTER PROCEDURE [dbo].[TestErrorLog]
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY---------开始捕捉异常
BEGIN TRANSACTION------------------开始事务
update ShanTest.dbo.Product set Name=NULL where Id=1--这里随便建一个数据库，建一个表，给该表中不允许为空的列插入一个NUll
--select 1/0;
COMMIT ----------------------------提交事务
END TRY-----------结束捕捉异常
BEGIN CATCH------有异常被捕获
IF @@TRANCOUNT > 0---------------------判断有没有事务
BEGIN
ROLLBACK --------------------------回滚事务
END
EXEC pro_ErrorLog----------------------执行存储过程将错误信息记录在表当中
END CATCH--------结束异常处理
END
执行改存储过程，进行测试：
USE [ShanTest]
GO
EXEC [dbo].[TestErrorLog]
GO
测试结果：
SQL Serve消息框中现实消息：
ErrorNumber ： 515
ErrorSeverity ： 16
ErrorState ：2
ErrorProcedure ：TestErrorLog
ErrorLine ：13
ErrorMessage ：不能将值 NULL 插入列 'Name'，表 'ShanTest.dbo.Product'；列不允许有 Null 值。UPDATE 失败。
该消息是由pro_PrintError存储过程打印的
同时ErrorLog表中添加了一条记录：