在SQL Server 2005数据库中记录存储过程错误
我对下面的TSQL存储过程使用下面的一般构造。这适用于将错误信息返回给调用应用程序代码。但是,我也想将错误记录在数据库本身中。我如何在SQL Server 2005中完成这项工作?在SQL Server 2005数据库中记录存储过程错误
BEGIN TRY
TSQL...
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
END CATCH
SET NOCOUNT OFF
SET @Error = @@ERROR
RETURN @Error
这里是回滚嵌套过程记录的例子...
这将创建两个测试表和三个过程,可以嵌套的方式与交易被称为:
if exists (select * from sysobjects where id = object_id(N'[dbo].ProcedureA') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].ProcedureA
if exists (select * from sysobjects where id = object_id(N'[dbo].ProcedureB') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].ProcedureB
if exists (select * from sysobjects where id = object_id(N'[dbo].ProcedureC') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].ProcedureC
if exists (select * from sysobjects where id = object_id(N'YourLogTable') and OBJECTPROPERTY(id, N'IsTable') = 1) drop table YourLogTable
if exists (select * from sysobjects where id = object_id(N'YourTestTable') and OBJECTPROPERTY(id, N'IsTable') = 1) drop table YourTestTable
go
CREATE TABLE YourLogTable
(
LogID int not null primary key identity(1,1)
,LogDate datetime not null default GETDATE()
,ProcedureName varchar(50) null default OBJECT_NAME(@@PROCID)
,LogText varchar(8000) not null
)
go
CREATE TABLE YourTestTable
(
TestID int not null primary key identity(1,1)
,TestData varchar(100) not null
)
go
-----------------------------------------------------------------------
-----------------------------------------------------------------------
CREATE PROCEDURE ProcedureA
(
@ParamA1 int
,@ParamA2 varchar(10)
,@ErrorMsg varchar(1000) OUTPUT
)
AS
DECLARE @LogValue varchar(8000)
DECLARE @ReturnValueX int
DECLARE @ErrorMsgX varchar(1000)
DECLARE @TempValue int
BEGIN TRY
SET @LogValue=ISNULL(OBJECT_NAME(@@PROCID), 'unknown')
+': @ParamA1='+COALESCE(''''+CONVERT(varchar(10),@ParamA1)+'''','null')
+', @ParamA2='+COALESCE(''''[email protected]+'''','null')
BEGIN TRANSACTION --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<,
INSERT INTO YourTestTable (TestData) VALUES ('I was in top of ProdecureA')
--your logic logic here---
IF @ParamA1=1
BEGIN
RAISERROR('testing, bad parameter',16,1) --send control to the BEGIN CATCH block
END
SET @[email protected]/@ParamA1
EXEC @ReturnValueX=ProcedureB @ParamA1,@ParamA2,@ErrorMsgX OUTPUT
IF @ReturnValueX!=0
BEGIN
SET @ErrorMsg='Call to ProcedureB failed, ReturnValueX='+COALESCE(''''+CONVERT(varchar(10),@ReturnValueX)+'''','null')+', @ErrorMsgX='+COALESCE(''''+CONVERT(varchar(10),@ErrorMsgX)+'''','null')
RAISERROR(@ErrorMsg,16,1) --send control to the BEGIN CATCH block
END
--your logic logic here---
INSERT INTO YourTestTable (TestData) VALUES ('I was in bottom of ProdecureA')
END TRY
BEGIN CATCH
IF XACT_STATE()!=0
BEGIN
ROLLBACK TRANSACTION
END
SET @[email protected]+'; '
+CASE WHEN ERROR_NUMBER() IS NOT NULL THEN 'Msg ' +CONVERT(varchar(30), ERROR_NUMBER() ) ELSE '' END
+CASE WHEN ERROR_SEVERITY() IS NOT NULL THEN ', Level ' +CONVERT(varchar(30), ERROR_SEVERITY() ) ELSE '' END
+CASE WHEN ERROR_STATE() IS NOT NULL THEN ', State ' +CONVERT(varchar(30), ERROR_STATE() ) ELSE '' END
+CASE WHEN ERROR_PROCEDURE() IS NOT NULL THEN ', Procedure ' + ERROR_PROCEDURE() ELSE '' END
+CASE WHEN ERROR_LINE() IS NOT NULL THEN ', Line ' +CONVERT(varchar(30), ERROR_LINE() ) ELSE '' END
+CASE WHEN ERROR_MESSAGE() IS NOT NULL THEN ', ' + ERROR_MESSAGE() ELSE '' END
INSERT INTO YourLogTable (LogText) VALUES (@ErrorMsg)
RETURN 999
END CATCH
IF XACT_STATE()!=0
BEGIN
COMMIT
END
--PRINT ISNULL(OBJECT_NAME(@@PROCID), 'unknown')+' OK'
RETURN 0
GO
-----------------------------------------------------------------------
-----------------------------------------------------------------------
CREATE PROCEDURE ProcedureB
(
@ParamB1 int
,@ParamB2 varchar(10)
,@ErrorMsg varchar(1000) OUTPUT
)
AS
DECLARE @LogValue varchar(8000)
DECLARE @ReturnValueX int
DECLARE @ErrorMsgX varchar(1000)
DECLARE @TempValue int
BEGIN TRY
SET @LogValue=ISNULL(OBJECT_NAME(@@PROCID), 'unknown')
+': @ParamB1='+COALESCE(''''+CONVERT(varchar(10),@ParamB1)+'''','null')
+', @ParamB2='+COALESCE(''''[email protected]+'''','null')
BEGIN TRANSACTION --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<,
INSERT INTO YourTestTable (TestData) VALUES ('I was in top of ProdecureB')
--your logic logic here---
IF @ParamB1=10
BEGIN
RAISERROR('testing, bad parameter',16,1) --send control to the BEGIN CATCH block
END
SET @[email protected]/@ParamB1
EXEC @ReturnValueX=ProcedureC @ParamB1,@ErrorMsgX OUTPUT
IF @ReturnValueX!=0
BEGIN
SET @ErrorMsg='Call to ProcedureC failed, ReturnValueX='+COALESCE(''''+CONVERT(varchar(10),@ReturnValueX)+'''','null')+', @ErrorMsgX='+COALESCE(''''+CONVERT(varchar(10),@ErrorMsgX)+'''','null')
RAISERROR(@ErrorMsg,16,1) --send control to the BEGIN CATCH block
END
--your logic logic here---
INSERT INTO YourTestTable (TestData) VALUES ('I was in bottom of ProdecureB')
END TRY
BEGIN CATCH
IF XACT_STATE()!=0
BEGIN
ROLLBACK TRANSACTION
END
SET @[email protected]+'; '
+CASE WHEN ERROR_NUMBER() IS NOT NULL THEN 'Msg ' +CONVERT(varchar(30), ERROR_NUMBER() ) ELSE '' END
+CASE WHEN ERROR_SEVERITY() IS NOT NULL THEN ', Level ' +CONVERT(varchar(30), ERROR_SEVERITY() ) ELSE '' END
+CASE WHEN ERROR_STATE() IS NOT NULL THEN ', State ' +CONVERT(varchar(30), ERROR_STATE() ) ELSE '' END
+CASE WHEN ERROR_PROCEDURE() IS NOT NULL THEN ', Procedure ' + ERROR_PROCEDURE() ELSE '' END
+CASE WHEN ERROR_LINE() IS NOT NULL THEN ', Line ' +CONVERT(varchar(30), ERROR_LINE() ) ELSE '' END
+CASE WHEN ERROR_MESSAGE() IS NOT NULL THEN ', ' + ERROR_MESSAGE() ELSE '' END
INSERT INTO YourLogTable (LogText) VALUES (@ErrorMsg)
RETURN 999
END CATCH
IF XACT_STATE()!=0
BEGIN
COMMIT
END
--PRINT ISNULL(OBJECT_NAME(@@PROCID), 'unknown')+' OK'
RETURN 0
GO
-----------------------------------------------------------------------
-----------------------------------------------------------------------
CREATE PROCEDURE ProcedureC
(
@ParamC1 int
,@ErrorMsg varchar(1000) OUTPUT
)
AS
DECLARE @LogValue varchar(8000)
DECLARE @ReturnValueX int
DECLARE @ErrorMsgX varchar(1000)
DECLARE @TempValue int
BEGIN TRY
BEGIN TRANSACTION --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<,
SET @LogValue=ISNULL(OBJECT_NAME(@@PROCID), 'unknown')
+': @ParamC1='+COALESCE(''''+CONVERT(varchar(10),@ParamC1)+'''','null')
--your logic logic here---
INSERT INTO YourTestTable (TestData) VALUES ('I was in top of ProdecureC')
IF @ParamC1=100
BEGIN
RAISERROR('testing, bad parameter',16,1) --send control to the BEGIN CATCH block
END
SET @[email protected]/@ParamC1
--your logic logic here---
INSERT INTO YourTestTable (TestData) VALUES ('I was in bottom of ProdecureC')
END TRY
BEGIN CATCH
IF XACT_STATE()!=0
BEGIN
ROLLBACK TRANSACTION
END
SET @[email protected]+'; '
+CASE WHEN ERROR_NUMBER() IS NOT NULL THEN 'Msg ' +CONVERT(varchar(30), ERROR_NUMBER() ) ELSE '' END
+CASE WHEN ERROR_SEVERITY() IS NOT NULL THEN ', Level ' +CONVERT(varchar(30), ERROR_SEVERITY() ) ELSE '' END
+CASE WHEN ERROR_STATE() IS NOT NULL THEN ', State ' +CONVERT(varchar(30), ERROR_STATE() ) ELSE '' END
+CASE WHEN ERROR_PROCEDURE() IS NOT NULL THEN ', Procedure ' + ERROR_PROCEDURE() ELSE '' END
+CASE WHEN ERROR_LINE() IS NOT NULL THEN ', Line ' +CONVERT(varchar(30), ERROR_LINE() ) ELSE '' END
+CASE WHEN ERROR_MESSAGE() IS NOT NULL THEN ', ' + ERROR_MESSAGE() ELSE '' END
INSERT INTO YourLogTable (LogText) VALUES (@ErrorMsg)
RETURN 999
END CATCH
IF XACT_STATE()!=0
BEGIN
COMMIT
END
--PRINT ISNULL(OBJECT_NAME(@@PROCID), 'unknown')+' OK'
RETURN 0
GO
这将运行四个测试用例上面的程序:
- 了失效,YourTestTable中没有任何数据,YourLogTable显示日志的信息 信息
- 无法在B,YourTestTable中没有任何数据,YourLogTable显示日志信息为B和A
- 无法在C,YourTestTable中没有任何数据,YourLogTable显示日志信息为C,B和A
-
所有工作,YourTestTable中有数据,YourLogTable中有
代码在这里没有数据:
DECLARE @ReturnValue int DECLARE @ErrorMsg varchar(1000) print '###########################################################################' print 'will error out in A - should log A>>> EXEC ProcedureA 1,''abcd''' delete from YourTestTable; delete from YourLogTable; EXEC @ReturnValue=ProcedureA 1,'abcd',@ErrorMsg OUTPUT; SELECT @ReturnValue AS '@ReturnValue' ,@ErrorMsg AS '@ErrorMsg'; select 'YourTestTable' AS 'YourTestTable',* from YourTestTable; select 'YourLogTable' AS 'YourLogTable',* from YourLogTable; print '###########################################################################' print 'will error out in B - should log B and A>>> EXEC ProcedureA 10,''abcd''' delete from YourTestTable; delete from YourLogTable; EXEC @ReturnValue=ProcedureA 10,'abcd',@ErrorMsg OUTPUT; SELECT @ReturnValue AS '@ReturnValue' ,@ErrorMsg AS '@ErrorMsg'; select 'YourTestTable' AS 'YourTestTable',* from YourTestTable; select 'YourLogTable' AS 'YourLogTable',* from YourLogTable; print '###########################################################################' print 'will error out in C - should log C, B and A>>>> EXEC ProcedureA 100,''123''' delete from YourTestTable; delete from YourLogTable; EXEC @ReturnValue=ProcedureA 100,'123',@ErrorMsg OUTPUT; SELECT @ReturnValue AS '@ReturnValue' ,@ErrorMsg AS '@ErrorMsg'; select 'YourTestTable' AS 'YourTestTable',* from YourTestTable; select 'YourLogTable' AS 'YourLogTable',* from YourLogTable; print '###########################################################################' print 'will complete, YourTestTable will contain data >>>> EXEC ProcedureA 2,''123''' delete from YourTestTable; delete from YourLogTable; EXEC @ReturnValue=ProcedureA 2,'123',@ErrorMsg OUTPUT; SELECT @ReturnValue AS '@ReturnValue' ,@ErrorMsg AS '@ErrorMsg'; select 'YourTestTable' AS 'YourTestTable',* from YourTestTable; select 'YourLogTable' AS 'YourLogTable',* from YourLogTable; print '###########################################################################'
这里是输出,这说明在日志中堆栈,尽管回滚:
###########################################################################
will error out in A - should log A>>> EXEC ProcedureA 1,'abcd'
(6 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
@ReturnValue @ErrorMsg
------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
999 ProcedureA: @ParamA1='1', @ParamA2='abcd'; Msg 50000, Level 16, State 1, Procedure ProcedureA, Line 33, testing, bad parameter
(1 row(s) affected)
YourTestTable TestID TestData
------------- ----------- ----------------------------------------------------------------------------------------------------
(0 row(s) affected)
YourLogTable LogID LogDate ProcedureName LogText
------------ ----------- ----------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
YourLogTable 37 2009-09-08 15:04:39.663 ProcedureA ProcedureA: @ParamA1='1', @ParamA2='abcd'; Msg 50000, Level 16, State 1, Procedure ProcedureA, Line 33, testing, bad parameter
(1 row(s) affected)
###########################################################################
will error out in B - should log B and A>>> EXEC ProcedureA 10,'abcd'
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
@ReturnValue @ErrorMsg
------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
999 ProcedureA: @ParamA1='10', @ParamA2='abcd'; Msg 266, Level 16, State 2, Procedure ProcedureB, Line 0, Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
(1 row(s) affected)
YourTestTable TestID TestData
------------- ----------- ----------------------------------------------------------------------------------------------------
(0 row(s) affected)
YourLogTable LogID LogDate ProcedureName LogText
------------ ----------- ----------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
YourLogTable 38 2009-09-08 15:04:39.680 ProcedureB ProcedureB: @ParamB1='10', @ParamB2='abcd'; Msg 50000, Level 16, State 1, Procedure ProcedureB, Line 31, testing, bad parameter
YourLogTable 39 2009-09-08 15:04:39.680 ProcedureA ProcedureA: @ParamA1='10', @ParamA2='abcd'; Msg 266, Level 16, State 2, Procedure ProcedureB, Line 0, Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
(2 row(s) affected)
###########################################################################
will error out in C - should log C, B and A>>>> EXEC ProcedureA 100,'123'
(0 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
@ReturnValue @ErrorMsg
------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
999 ProcedureA: @ParamA1='100', @ParamA2='123'; Msg 266, Level 16, State 2, Procedure ProcedureB, Line 0, Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
(1 row(s) affected)
YourTestTable TestID TestData
------------- ----------- ----------------------------------------------------------------------------------------------------
(0 row(s) affected)
YourLogTable LogID LogDate ProcedureName LogText
------------ ----------- ----------------------- -------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
创建列(MessageText中,严重性,状态,DateTimeOccurred)和INSERT
信息到它的错误表。
创建ErrorLog表并在CATCH块中写入此表。
这不是那么容易,因为,虽然......
但是,你要测试“@@ TRANCOUNT = 0”,首先是因为它稍后将回滚,说如果你巢存储与程序TRY/CATCH或进行客户交易。如果你使用SET XACT_ABORT ON
这意味着你可能有多个错误信息,如果您有嵌套这并不适用,所以我都登录和ERROR_PROCEDURE()
太OBJECT_NAME(@@PROCID)
区分两种情况的发生错误,并在那里它被记录下来。
简单的答案是'写入日志表',但实际上这有点复杂,因为您制作的任何表都受到当前事务的影响,并且catch块受到错误处理的影响三态交易。您必须使用XACT_STATE来检查当前事务状态。如果是一个注定要失败的事务(状态-1),那么您必须先回滚然后再登录,否则尝试插入日志表会导致批处理异常终止。
有关使用T-SQL try/catch块并正确处理事务的过程模板示例,请参见Exception handling and nested transactions。
从长远来看,混合的try-catch逻辑*和*返回错误代码不会起作用。保证有人会忘记检查一个程序返回代码,并依靠存在已经回滚的交易来继续前进!无论单独依靠异常还是通过使用保存点正确处理XACT_STATE的所有3个状态,从而允许调用者决定是否在错误情况下回滚或在其他路径上继续,都可以说是更好。许多错误是可以恢复的。请参阅http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/ – 2009-09-22 22:10:56
谢谢你这样一个彻底的例子! – javacavaj 2009-09-23 02:13:24
@Remus Rusanu,我们从不使用保存点并总是回滚,所以调用者没有多少选项,因为事务计数不匹配,并且会自动进入catch。 – 2009-09-23 13:17:15