[MSSQL] try..catch..rollback 예제

ALTER PROCEDURE dbo.INSERT_ACTION_HISTORY
--CREATE PROCEDURE dbo.INSERT_ACTION_HISTORY
        @ResultDate datetime,
	@LOGID bigint,
	@ActionCode int,
	@Description varchar(500) = NULL
AS
BEGIN
	SET NOCOUNT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		INSERT INTO TBL_SAMPLE (LOGID, ActionCode, Description, UpdateDate) 
		VALUES (@LOGID, @ActionCode, @Description, GETDATE());
	END TRY
    BEGIN CATCH
        -- 파라미터 정보도 저장 가능 (스트링 만들때 오류 안나도록 반드시 테스트 후에 적용할 것)
        DECLARE @ParamInfo VARCHAR(1000) = '';
	SET @ParamInfo = 
		  '입력파라미터: '
		+ '@ResultDate=[' + ISNULL(CONVERT(VARCHAR(19), @ResultDate, 120), 'null')
		+ '], @LOGID=['+CASE WHEN @LOGID IS NULL THEN 'null' ELSE CAST(@LOGID AS VARCHAR) END
		+ '], @ActionCode=['+CASE WHEN @ActionCode IS NULL THEN 'null' ELSE CAST(@ActionCode AS VARCHAR) END
		+ '], @Description=['+CASE WHEN @Description IS NULL THEN 'null' ELSE SUBSTRING(@Description, 0, 100) END
		+ ']';
		PRINT @ParamInfo;

	-- 이 부분에서 DB에러 이력 테이블에 저장하거나 출력
      SELECT ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;
      IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;
   END CATCH	
   
   BEGIN
	IF @@TRANCOUNT > 0   
		COMMIT TRANSACTION;
   END

ADD 2019.08.13 ErrorLog테이블 생성 및 에러발생 기록 예제

https://www.sqlservercentral.com/articles/conditional-set-based-processing-moving-towards-a-best-practice-for-etl