개발 과정이나 서비스 중에 있는 데이터베이스에서 발생하는 에러를 테이블로 관리하여
실시간 집계 및 분석을 위한 스크립트이며 AdventureWorks 에 있는 sp 를 수정하였습니다. ^^
/*------------------------------------------------------------
title : MS-SQL2005 - Try Catch를통한구조화된에러처리
description : error log를테이블로관리하여보여주자.
SQL2000 에서사용하는에러처리는모든구문마다@@error 를받아서처리해주고
goto 문을사용하는등구조적이지못한방법으로처리했는데
SQL2005에사용되는try ..catch 문으로에러처리를할수있게되었습니다.
트랜젝션을사용하는경우XACT_STATE() 함수를이용하여처리하시면됩니다.
- ERROR_NUMBER() : 50000 이상: 사용자가정의한오류
- 개발자가정의한오류(SEVERITY(심각도) : 11-19 지정)
------------------------------------------------------------*/
-- 1. 테이블만들기
CREATE TABLE [dbo].[tbl_error_log](
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[ErrorTime] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_ErrorTime] DEFAULT (getdate()),
[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 )
)
create index ix_tbl_error_log_ErrorNumber on tbl_error_log(ErrorNumber)
GO
-- 2. 에러기록하기
CREATE PROCEDURE [dbo].[usp_error_log]
@ERROR_NUMBER_OUTPUT [int] OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ERROR_NUMBER INT
BEGIN TRY
IF ERROR_NUMBER() IS NULL
RETURN;
IF XACT_STATE() = -1
BEGIN
PRINT 'Cannot log error since the current transaction is in an uncommittable state. '
+ 'Rollback the transaction before executing usp_error_log '
+ 'in order to successfully log error information.';
RETURN;
END
IF (ERROR_NUMBER() = 50000)
BEGIN
SET @ERROR_NUMBER = @ERROR_NUMBER_OUTPUT
END
ELSE
BEGIN
SET @ERROR_NUMBER = ERROR_NUMBER()
END
INSERT [dbo].[tbl_error_log]
(
[UserName],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[ErrorProcedure],
[ErrorLine],
[ErrorMessage]
)
VALUES
(
CONVERT(sysname, CURRENT_USER),
@ERROR_NUMBER,
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE() + N' by ' + host_name() + N' '
+ convert(nvarchar(256), ISNULL(SUSER_SNAME(), SUSER_NAME()))
);
-- Pass back the ErrorLogID of the row inserted
-- SET @ErrorLogID = @@IDENTITY;
IF ( @ERROR_NUMBER_OUTPUT < 50000 )
BEGIN
SET @ERROR_NUMBER_OUTPUT = ERROR_NUMBER();
END
END TRY
BEGIN CATCH
PRINT 'An error occurred in stored procedure usp_error_log: ';
-- 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();
RETURN -1;
END CATCH
END;
GO
/*------------------------------------------------------------
-- Test Script --
------------------------------------------------------------*/
create table tbl_test_errorlog(
int_col int ,
var10_col varchar(10)
)
-- drop proc dbo.usp_test_errorlog
create proc dbo.usp_test_errorlog
@int_col int = 0
, @var10_col varchar(10) = 'a'
as
SET NOCOUNT ON;
SET LOCK_TIMEOUT 10000;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET XACT_ABORT ON;
declare @err_num int; set @err_num = 0 ;
begin try
begin tran
if (@int_col = 0 )
begin
insert into tbl_test_errorlog(int_col) values(@var10_col);
end
if(@int_col > 0 )
begin
set @err_num = 50003 ;
raiserror ('error#%d: @var10_col = %s, @int_col = %d', 11, 1, @err_num, @var10_col, @int_col );
end
commit tran
end try
begin catch
if ( xact_state() = -1 )
begin
--print 'uncommitable rollback tran';
rollback tran
end
else if ( xact_state() = 1 )
begin
--print 'rollback tran';
rollback tran
end
exec dbo.usp_error_log @err_num output ;
end catch
go
exec usp_test_errorlog 0, 'a';
exec usp_test_errorlog 1, 'a';
select * from [tbl_error_log]
-- 1
-- varchar 값'a'을(를) 데이터형식int(으)로변환하지못했습니다. by ryuism
-- 2
-- error#50003: @var10_col = a, @int_col = 1 by ryuism