SqlServer Transaction 처리 SP Sample

예전에는 

IF @@ERROR <> 0   
과 같은 구문으로 에러 발생 캐치해서 ROLLBACK 처리했던거 같은데

서핑해보니 그거보단 TRY CATCH 를 권장하는 분위기인거 같아서 서핑후 적용해보았는데 잘되는거 같다.

참고 : http://msdn.microsoft.com/en-us/library/ms175976.aspx  <== 아주 잘 나와있음!!

http://stackoverflow.com/questions/6252757/stored-procedure-transaction

아래 프로시저는 전자결재 서버로부터 받은 XML을 읽어서 쌓아놓은 테이블을

읽어서 관련 처리후 백업테이블로 넘기는 프로시저다.

ALTER PROCEDURE [dbo].[SP_APPLY_APRV_RECV_RETURN]

(

@RETURN_MSG VARCHAR(8000) OUTPUT

)

AS

BEGIN

/*

결재연동 수신 테이블을 읽어서 관련 업무데이터를 업데이트후

해당건은 결재수신 보관테이블을 이동시킨다.

eg)

EXEC SP_APPLY_APRV_RECV

===========================================

TRY 문과 CATCH 각각에서 @RETURN_MSG 값 세팅한것은 서로 읽지는 못함.

*/

/******************************

*  Variable Declarations

*******************************/

    DECLARE     @ErrorCode  int  

    DECLARE     @ErrorStep  varchar(200)

DECLARE @TEMP_MSG VARCHAR(2000)

/******************************

*  Initialize Variables

*******************************/

    SET @ErrorCode = @@ERROR

   

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare @V_ONNARA_SERVER_ID CHAR(12)

declare @V_PSS_SERVER_ID CHAR(12)

SET @V_ONNARA_SERVER_ID = 'ADM141000040'

SET @V_PSS_SERVER_ID = 'PSSPSS123456'

declare @C_RECV_NUM BIGINT

declare @C_ADMIN_NUM VARCHAR(20)

declare @C_RECV_FILE_NAME VARCHAR(80)

declare @C_SEND_SERVER_ID CHAR(12)

declare @C_RECV_SERVER_ID CHAR(12)

declare @C_SEND_USER_ID VARCHAR(20)

declare @C_RECV_USER_ID VARCHAR(20)

declare @C_MAIN_TYPE CHAR(7)

declare @C_SUB_TYPE VARCHAR(10)

declare @C_DOC_TYPE VARCHAR(20)

declare @C_INS_DATE DATETIME

declare @CL_RECV_APLY_RSLT CHAR(1)

declare @CL_PROC_DOCU_ID CHAR(8)

Declare C CURSOR FOR

SELECT 

RECV_NUM, ADMIN_NUM, RECV_FILE_NAME

, SEND_SERVER_ID, RECV_SERVER_ID

, SEND_USER_ID, RECV_USER_ID

, MAIN_TYPE, SUB_TYPE, DOC_TYPE

, INS_DATE

FROM T_APRV_RECV

WHERE INS_DATE >= 

ISNULL( 

( SELECT INS_DATE 

FROM T_APRV_RECV_ARCV 

WHERE RECV_NUM = ( SELECT MAX(RECV_NUM) FROM T_APRV_RECV_ARCV )

)

, CONVERT(DATETIME,'2012-01-01', 20) 

)

ORDER BY RECV_NUM ASC

OPEN C

FETCH NEXT FROM C INTO 

@C_RECV_NUM

,@C_ADMIN_NUM

,@C_RECV_FILE_NAME 

,@C_SEND_SERVER_ID 

,@C_RECV_SERVER_ID

,@C_SEND_USER_ID 

,@C_RECV_USER_ID 

,@C_MAIN_TYPE 

,@C_SUB_TYPE 

,@C_DOC_TYPE 

,@C_INS_DATE

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN TRANSACTION;

BEGIN TRY

-- EACH LOOP INIT

SET @CL_RECV_APLY_RSLT = NULL

SET @CL_PROC_DOCU_ID = NULL

PRINT '============================================='

PRINT '@C_ADMIN_NUM:' + @C_ADMIN_NUM

/*

PRINT '@C_RECV_NUM:' + @C_RECV_NUM

PRINT '@C_SEND_SERVER_ID:' + @C_SEND_SERVER_ID

PRINT '@C_RECV_SERVER_ID:' + @C_RECV_SERVER_ID

PRINT '@C_SEND_USER_ID:' + @C_SEND_USER_ID

PRINT '@C_RECV_USER_ID:' + @C_RECV_USER_ID

PRINT '@C_MAIN_TYPE:' + @C_MAIN_TYPE

PRINT '@C_SUB_TYPE:' + @C_SUB_TYPE

PRINT '@C_DOC_TYPE:' + @C_DOC_TYPE

PRINT '@C_INS_DATE:' + @C_INS_DATE

*/

SET @ErrorStep = 'Error in Checking Server Id'

 

IF @C_SEND_SERVER_ID = @V_ONNARA_SERVER_ID AND @C_RECV_SERVER_ID = @V_PSS_SERVER_ID

BEGIN

IF LOWER(@C_DOC_TYPE) IN ( 'arrive', 'receive', 'req-resend', 'submit', 'temporary-save')

SET @CL_RECV_APLY_RSLT = 'X'

ELSE IF LOWER(@C_DOC_TYPE) IN ( 'approval' )

SET @CL_RECV_APLY_RSLT = 'S'

ELSE IF LOWER(@C_DOC_TYPE) IN ( 'fail', 'invalid', 'return' )

SET @CL_RECV_APLY_RSLT = 'F'

ELSE IF LOWER(@C_DOC_TYPE) IN ( 'consider', 'review', 'cease', 'retrieval' )

SET @CL_RECV_APLY_RSLT = 'F'

ELSE

SET @CL_RECV_APLY_RSLT = 'F'

IF @CL_RECV_APLY_RSLT IN ( 'S', 'F' )

/*

SELECT @CL_PROC_DOCU_ID = PROC_DOCU_ID 

FROM T_APRV_SEND WHERE ADMIN_NUM = @C_ADMIN_NUM

IF @CL_PROC_DOCU_ID IS NOT NULL

UPDATE 

WHERE PROC_DOCU_ID = 

*/

SET @ErrorStep = 'Error in Updating'

UPDATE T_PROC_DOCU

SET PROC_STATUS = CASE WHEN @CL_RECV_APLY_RSLT = 'S' THEN '7'

ELSE '5'

END

WHERE PROC_DOCU_ID = ( SELECT PROC_DOCU_ID FROM T_APRV_SEND WHERE ADMIN_NUM = @C_ADMIN_NUM )

END

PRINT '@C_DOC_TYPE:' + @C_DOC_TYPE

PRINT '@CL_RECV_APLY_RSLT:' + @CL_RECV_APLY_RSLT

SET @ErrorStep = 'Error in Inserting'

INSERT INTO T_APRV_RECV_ARCV

(

RECV_NUM, ADMIN_NUM, RECV_FILE_NAME

, SEND_SERVER_ID, RECV_SERVER_ID

, SEND_USER_ID, RECV_USER_ID

, MAIN_TYPE, SUB_TYPE, DOC_TYPE

, RECV_APLY_RSLT

, INS_DATE

)

VALUES

(

@C_RECV_NUM

,@C_ADMIN_NUM

,@C_RECV_FILE_NAME 

,@C_SEND_SERVER_ID 

,@C_RECV_SERVER_ID 

,@C_SEND_USER_ID 

,@C_RECV_USER_ID 

,@C_MAIN_TYPE 

,@C_SUB_TYPE 

,@C_DOC_TYPE 

,@CL_RECV_APLY_RSLT

,@C_INS_DATE

)

SET @ErrorStep = 'Error in Deleting'

DELETE FROM T_APRV_RECV

WHERE RECV_NUM = @C_RECV_NUM

-- IF @RETURN_MSG IS NOT NULL 

-- SET @RETURN_MSG = @RETURN_MSG + '||'

   SET @TEMP_MSG = 

'RECV_NUM:' + CAST(@C_RECV_NUM AS VARCHAR(20)) + ',RECV_RSLT:SUCC,'

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK TRANSACTION;

/*

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;

*/

SET @TEMP_MSG = 'RECV_NUM:' + CAST(@C_RECV_NUM AS VARCHAR(20)) + ',RECV_RSLT:FAIL,'

+ @ErrorStep + ' '

+ cast(ERROR_NUMBER() as varchar(20)) + ' line: '

+ cast(ERROR_LINE() as varchar(20)) + ' ' 

+ ERROR_MESSAGE() 

-- + ' > ' + ERROR_PROCEDURE()

END CATCH;

PRINT @TEMP_MSG

IF @RETURN_MSG IS NOT NULL

SET @RETURN_MSG = @RETURN_MSG + CHAR(13) + CHAR(10) + @TEMP_MSG

ELSE

SET @RETURN_MSG = @TEMP_MSG

IF @@TRANCOUNT > 0

   COMMIT TRANSACTION;

FETCH NEXT FROM C INTO 

@C_RECV_NUM

,@C_ADMIN_NUM

,@C_RECV_FILE_NAME 

,@C_SEND_SERVER_ID 

,@C_RECV_SERVER_ID 

,@C_SEND_USER_ID 

,@C_RECV_USER_ID 

,@C_MAIN_TYPE 

,@C_SUB_TYPE 

,@C_DOC_TYPE 

,@C_INS_DATE

END

CLOSE C

DEALLOCATE C

END