龙华网站开发公司,营销型网站主机,邮箱格式,网站开发时间进度SQLserver 存储过程执行错误记录到表 From: http://blog.csdn.net/leshami/article/details/51333650 对于在执行存储过程中碰到的一些错误#xff0c;如果未及时捕获或者说传递给前端应用程序来#xff0c;在这样的情形下#xff0c;故障的排查显得尤为困难。基于此… SQLserver 存储过程执行错误记录到表 From: http://blog.csdn.net/leshami/article/details/51333650 对于在执行存储过程中碰到的一些错误如果未及时捕获或者说传递给前端应用程序来在这样的情形下故障的排查显得尤为困难。基于此我们可以可以将这些错误信息记录到日志同时也可以将其传递给前端应用程序。如本文的示例。一、将错误信息记录到表CREATE TABLE ErrorLog( ID INT IDENTITY (1,1) NOT NULL, ErrorNumber INT, ErrorMessage VARCHAR(1000), ErrorSeverity INT, ErrorState INT, ErrorLine INT, ErrorProcedure VARCHAR(128), ErrorDate datetime);CREATE PROCEDURE CatchErrorDemoAS --相关业务逻辑 BEGIN TRY SELECT 1 / 0; END TRY--相关错误捕获 BEGIN CATCH PRINT Error Number: CAST(ERROR_NUMBER() AS VARCHAR(10)); PRINT Error Serverity: CAST(ERROR_SEVERITY() AS VARCHAR(10)); PRINT Error State: CAST(ERROR_STATE() AS VARCHAR(10)); PRINT Error Procedure: ERROR_PROCEDURE(); PRINT Error Line: CAST(ERROR_LINE() AS VARCHAR(10)); PRINT Error Message: ERROR_MESSAGE(); INSERT INTO ErrorLog ( ErrorNumber , ErrorSeverity , ErrorState , ErrorProcedure , ErrorLine , ErrorMessage , ErrorDate ) VALUES ( ERROR_NUMBER() , ERROR_SEVERITY() , ERROR_STATE() , ERROR_PROCEDURE() , ERROR_LINE() , ERROR_MESSAGE() , GETDATE() ) END CATCH;--执行相关存储过程并查询日志表同时与系统消息表进行对比EXEC CatchErrorDemo;SELECT * FROM ErrorLog;二、传递错误信息CREATE PROC CatchErrorDemo2 a INT , rtn VARCHAR(20) OUTPUT , rtn_message VARCHAR(200) OUTPUTAS BEGIN BEGIN TRY DECLARE result INT; SET result A a; END TRY BEGIN CATCH SET rtn ERROR; SET rtn_message Error Number: CAST(ERROR_NUMBER() AS VARCHAR(10)) Error Serverity: CAST(ERROR_SEVERITY() AS VARCHAR(10)) Error State: CAST(ERROR_STATE() AS VARCHAR(10)) Error Procedure: ERROR_PROCEDURE() Error Line: CAST(ERROR_LINE() AS VARCHAR(10)) Error Message: ERROR_MESSAGE(); END CATCH; END;GO--调用存储过程DECLARE rtn INT , rtn_message VARCHAR(200);EXEC CatchErrorDemo2 0, rtn OUTPUT, rtn_message OUTPUT;PRINT rtn;PRINT rtn_message;--Result:245Error Number: 245 Error Serverity: 16 Error State: 1 Error Procedure: CatchErrorDemo2 Error Line: 10 Error Message: 在将 varchar 值 A 转换成数据类型 int 时失败。 转载于:https://blog.51cto.com/57388/1859828
