MS-SQL 저장 프로시저(Stored Procedure) 작성 예제

MS-SQL 저장 프로시저(Stored Procedure) 작성 예제

개요

이번 글에서는 MS-SQL의 저장 프로시저(Stored Procedure) 중 하나인 sp_ETCL_ORG_R1에 대해 분석하고, 그 역할과 동작 방식에 대해 설명하겠습니다. 이 프로시저는 I-GATE 시스템에서 조직 정보를 가져와 CMDB(Configuration Management Database)에 반영하는 역할을 합니다.

 

ex) SP 내용

USE [ITSM_Interface]
GO
/****** Object:  StoredProcedure [dbo].[sp_ETCL_ORG_R1]    Script Date: 03/04/2024 09:02:42 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER on
GO

 

 

ALTER PROC [dbo].[sp_ETCL_ORG_R1]
/*******************************************************************************
프로시져 명 : SP_ETCL_ORG_R1
DESCRIPTION : I-GATE에서 조직정보를 입수하여 CMDB에 넘겨준다
ITSM 개발자

EXEC SP_ETCL_ORG_R1
*********************************************************************************/ 

AS
BEGIN 

 DECLARE @RC     INT
 DECLARE @ERR_NO    INT

 DECLARE @STEP   VARCHAR(100)
 DECLARE @STA_TM  DATETIME
 DECLARE @END_TM  DATETIME
 DECLARE @PGM_NM  VARCHAR(100)

 SET @PGM_NM = 'SP_ETCL_ORG_R1'

 SET @STEP ='시작'
 SET @STA_TM = CURRENT_TIMESTAMP
 SET @END_TM = CURRENT_TIMESTAMP

 EXECUTE UP_ETCL_WRITE_TASK_LOG @P_PGM_NM  = @PGM_NM,
     @P_STEP_NM  = @STEP,
                                   @P_STA_TM = @STA_TM ,
     @P_END_TM = @END_TM,
     @P_ROW_CNT = 0,
     @P_ERR_NO  = 0     
 
 SET @STA_TM = CURRENT_TIMESTAMP

/* --------------------------------------------------------------------------------------------- */  
/*  TEMP 데이터를  CMDB에 맞게 CONVECTION STAGING                             */
/* --------------------------------------------------------------------------------------------- */
 EXEC @RC = UP_ETCL_ORG_CONVECTION_R1 
 IF @RC <> 0 OR @@ERROR <> 0 GOTO QuitWithError
 
/* --------------------------------------------------------------------------------------------- */  
/*  TEMP 데이터를  CMDB에  추가                                   */
/* --------------------------------------------------------------------------------------------- */
 EXEC @RC =UP_ETCL_ORG_CMDB_INSERT_R1
 IF @RC <> 0 OR @@ERROR <> 0 GOTO QuitWithError
   

 SET @STEP ='종료'
 SELECT @END_TM = CURRENT_TIMESTAMP

  EXECUTE UP_ETCL_WRITE_TASK_LOG @P_PGM_NM  = @PGM_NM,
     @P_STEP_NM  = @STEP,
                                   @P_STA_TM = @STA_TM ,
     @P_END_TM = @END_TM,
     @P_ROW_CNT = 0,
     @P_ERR_NO  = 0        


 GOTO EndSave
 
 
 QuitWithError:
 
  IF @@TRANCOUNT > 0 ROLLBACK
  --
  DECLARE @ERR_MSG  VARCHAR(300)
  SET @RC = ISNULL(@RC,0) + ISNULL(@@ERROR,0) 
  
  SELECT @ERR_MSG = MAX(CASE WHEN ERROR = @@ERROR THEN DESCRIPTION ELSE '' END)
             + MAX(CASE WHEN ERROR = @RC THEN DESCRIPTION ELSE '' END)        
  FROM master.dbo.sysmessages
  WHERE ERROR =ISNULL(@RC,0) + ISNULL(@@ERROR,0)
  AND MSGLANGID = 1042
  
  SET @STEP = @STEP+' 에서 ['+ @ERR_MSG +'] 오류가 발생했습니다.'
 

   EXECUTE UP_ETCL_WRITE_TASK_LOG  @P_PGM_NM =@PGM_NM,@P_STEP_NM =@STEP,
                                 @P_STA_TM=@STA_TM ,@P_END_TM=@END_TM,@P_ROW_CNT=0,@P_ERR_NO = 0   

  PRINT 'sp_ETCL_ORG_R1 오류 종료.'

  RETURN @ERR_NO       
                      
 EndSave:
  

  PRINT 'sp_ETCL_ORG_R1 가 정상적으로 수행되었습니다.'

  RETURN 0


END

---

프로시저 개요

1. 프로시저 명칭 및 목적

• 프로시저명: sp_ETCL_ORG_R1
• 설명: I-GATE에서 조직 정보를 가져와 CMDB에 저장하는 기능 수행
• 작성자: ITSM 개발자
• 실행 방법:

  EXEC sp_ETCL_ORG_R1

---

주요 처리 과정

1. 변수 선언 및 초기화

DECLARE @RC     INT
DECLARE @ERR_NO INT
DECLARE @STEP   VARCHAR(100)
DECLARE @STA_TM DATETIME
DECLARE @END_TM DATETIME
DECLARE @PGM_NM VARCHAR(100)

• @RC: 실행 결과 반환값 저장
• @ERR_NO: 오류 번호 저장
• @STEP: 현재 진행 단계
• @STA_TM, @END_TM: 시작 및 종료 시간 기록
• @PGM_NM: 프로시저명 저장

초기화 코드:

SET @PGM_NM = 'SP_ETCL_ORG_R1'
SET @STEP = '시작'
SET @STA_TM = CURRENT_TIMESTAMP
SET @END_TM = CURRENT_TIMESTAMP

 

이후, 실행 로그를 남기기 위해 UP_ETCL_WRITE_TASK_LOG 프로시저를 호출합니다.

EXECUTE UP_ETCL_WRITE_TASK_LOG @P_PGM_NM = @PGM_NM,
     @P_STEP_NM  = @STEP,
     @P_STA_TM = @STA_TM ,
     @P_END_TM = @END_TM,
     @P_ROW_CNT = 0,
     @P_ERR_NO  = 0

---

2. TEMP 데이터를 CMDB용으로 변환

EXEC @RC = UP_ETCL_ORG_CONVECTION_R1
IF @RC <> 0 OR @@ERROR <> 0 GOTO QuitWithError

• UP_ETCL_ORG_CONVECTION_R1 프로시저를 실행하여, TEMP 데이터를 CMDB 형식에 맞게 변환합니다.
• 오류 발생 시 QuitWithError로 이동합니다.

---

3. CMDB에 데이터 추가

EXEC @RC = UP_ETCL_ORG_CMDB_INSERT_R1
IF @RC <> 0 OR @@ERROR <> 0 GOTO QuitWithError

• UP_ETCL_ORG_CMDB_INSERT_R1을 실행하여 데이터를 CMDB에 추가합니다.
• 오류 발생 시 QuitWithError로 이동합니다.

---

4. 정상 종료 처리

SET @STEP = '종료'
SELECT @END_TM = CURRENT_TIMESTAMP
EXECUTE UP_ETCL_WRITE_TASK_LOG @P_PGM_NM = @PGM_NM,
     @P_STEP_NM  = @STEP,
     @P_STA_TM = @STA_TM ,
     @P_END_TM = @END_TM,
     @P_ROW_CNT = 0,
     @P_ERR_NO  = 0

• 정상 종료 시 UP_ETCL_WRITE_TASK_LOG를 호출하여 로그를 기록합니다.
• PRINT 문을 통해 성공 메시지를 출력합니다.

PRINT 'sp_ETCL_ORG_R1 가 정상적으로 수행되었습니다.'
RETURN 0

---

오류 처리

1. 오류 발생 시 롤백 및 로그 기록

QuitWithError:
IF @@TRANCOUNT > 0 ROLLBACK

• 오류가 발생하면 트랜잭션을 롤백하여 데이터 정합성을 유지합니다.

DECLARE @ERR_MSG  VARCHAR(300)
SET @RC = ISNULL(@RC,0) + ISNULL(@@ERROR,0)
SELECT @ERR_MSG = MAX(CASE WHEN ERROR = @@ERROR THEN DESCRIPTION ELSE '' END)
             + MAX(CASE WHEN ERROR = @RC THEN DESCRIPTION ELSE '' END)        
  FROM master.dbo.sysmessages
  WHERE ERROR = ISNULL(@RC,0) + ISNULL(@@ERROR,0)
  AND MSGLANGID = 1042

• 오류 메시지를 sysmessages 테이블에서 가져와 @ERR_MSG 변수에 저장합니다.

SET @STEP = @STEP+' 에서 ['+ @ERR_MSG +'] 오류가 발생했습니다.'
EXECUTE UP_ETCL_WRITE_TASK_LOG  @P_PGM_NM =@PGM_NM,@P_STEP_NM =@STEP,
     @P_STA_TM=@STA_TM ,@P_END_TM=@END_TM,@P_ROW_CNT=0,@P_ERR_NO = 0   

• UP_ETCL_WRITE_TASK_LOG를 호출하여 오류 로그를 기록합니다.

PRINT 'sp_ETCL_ORG_R1 오류 종료.'
RETURN @ERR_NO

• 오류 종료 메시지를 출력하고, 오류 코드를 반환합니다.

---

결론

sp_ETCL_ORG_R1 프로시저는 I-GATE에서 조직 정보를 가져와 CMDB에 저장하는 기능을 수행하며, 주요 프로세스는 다음과 같습니다:

1. 실행 로그 기록 (UP_ETCL_WRITE_TASK_LOG 호출)
2. TEMP 데이터를 CMDB용으로 변환 (UP_ETCL_ORG_CONVECTION_R1 호출)
3. CMDB에 데이터 추가 (UP_ETCL_ORG_CMDB_INSERT_R1 호출)
4. 정상 실행 종료 시 로그 기록 및 성공 메시지 출력
5. 오류 발생 시 롤백, 오류 메시지 기록 및 반환

이 프로시저는 데이터 정합성을 유지하면서 오류를 효과적으로 처리할 수 있도록 설계되어 있으며, 로그를 남겨 실행 이력을 추적할 수 있는 것이 특징입니다. 이를 활용하면 ITSM 시스템에서 조직 정보를 효과적으로 관리할 수 있습니다.