[MS-SQL] 프로시저 오류 발생 시 메일 발송(장단점, SMTP, 스케줄러)

#. 지향하는 방법은 아니지만 프로시저를 쓰는 곳이 있기에 작성해겠습니다.

 

#. 프로시저는 많은 SQL을 한 번에 처리하기 위한 DBMS에서 제공하는 실행 기능입니다.

 

#. 장점보단 단점이 부각되어 보이긴 하나 프로시저를 쓰는 곳은 계속해서 쓰는 것 같습니다.

---

#. 프로시저의 장단점

• 장점
  
  1. 요청 한 번에 여러 SQL을 실행이 가능하고 속도가 빠릅니다.
    
    - 한 프로시저 내 여러 개의 SQL을 작성하고 서비스가 DB에 접근을 한 번만 하면 되므로 네트워크 측면의 속도가 빠릅니다.
  
  2. DBMS에서 프로시저에 대한 관리가 편합니다.
  
      - 프로시저에 대한 내용들을 캡슐화하여 저장하고 이를 DBMS가 저장한 프로시저별로 보여주기에 보기 편합니다.

---

• 단점
  
  1. 유지보수가 어렵습니다.
  
      - 프로시저는 일반적인 SQL을 작성하는 것과 일부 다른 부분이 있고 많은 변수, 호출해 온 변수, 매우 긴 SQL 등으로 디버깅 및 이해하기 어렵습니다.
  
  2. 테스트하기 어렵습니다.
  
      - 위 언급한 바와 같이 작성 방법이 일반 SQL 작성방법과 다른 것이 있기에 테스트를 위해선 실행한 SQL을 변수에 담고 그 변수를 print(변수) 기능을 추가한 후 해당 프로시저를 실행시켜야 정확히 실행된 SQL을 뽑을 수 있습니다.
  
      - 또한 실행하여 얻은 값 또한 변수에 넣어 print(변수) 기능으로 봐야 알 수 있습니다.
  
      - 데이터가 다르면 다른 결과를 도출할 수 있기에 정확한 테스트를 위해선 개발, 운영 DB의 데이터가 동일한 상태여야지만 신뢰성이 있습니다.
  
  3. 버전관리가 어렵습니다.
  
      - 프로시저의 경우 수정할 부분을 수정하고 F5를 누르면 실행되고 이를 닫은 후 다시 열면 이전 내용을 알 수가 없습니다.
  
      - 따라서 이전 쿼리를 별도로 백업해 두는 방법이 유일한 것 같습니다.

 

---

#. 메일 발송 방법

• Step 1. 조회해 온 결과가 1개 이상일 경우 메일 보내는 프로시저를 생성.

USE [서버명]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_INDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[프로시저명]
AS
BEGIN
    SET NOCOUNT ON
    
    DECLARE @RESULT INT
    
    SELECT @RESULT = COUNT(*)
    FROM 테이블
    WHERE 컬럼명 = 조건
END

 

---

• Step 2-1-1. 오류 발생시킨 후 스케줄러 알림 기능 활용.
  
  - @ErrorMessage = 메일로 전달할 에러메시지 변수
  
  - @ErrorState = 에러의 위치 지정
  
  - RAISERROR(메시지, 심각도 수준, 상태)
      - 심각도 수준
          - 0 ~ 10   - 심각하지 않은 오류 / 시스템 오류를 발생시키지 않음
          - 11 ~ 16 - 사용자가 수정할 수 있는 오류
          - 17 ~ 19 - 사용자가 수정할 수 없는 S/W 오류
          - 20 ~ 24 - 시스템 문제를 나타내는 심각한 오류

BEGIN
    SET NOCOUNT ON
    
    DECLARE @RESULT INT
    
    SELECT @RESULT = COUNT(*)
    FROM 테이블
    WHERE 컬럼명 = 조건
    
    IF @RESULT > 0
    	BEGIN
            DECLARE @ErrorMessage VARCHAR(100) = '에러메시지 내용';
            DECLARE @ErrorState INT = 1;
            RAISERROR(@ErrorMessage, 16, @ErrorState);
     	END
END

 

---

• Step 2-1-2. 운영자 만들기 및 스케줄러 설정.
  
  - 이름과 메일 주소를 넣고 확인

---

---

 

- 일반, 단계, 일정에 대한 내용을 입력하고 알림에서 전자메일 > 운영자 선택 후 확인하면 프로시저에 작성한 쿼리가 0보다 클 경우 에러를 발생시키고 발생한 에러로 인해 스케줄러는 작업에 실패하며 선택한 운영자의 메일로 에러메시지를 발송합니다.

 

---

• Step 2-2-1. SMTP로 메일 보내기

 

---

---

---

1. 프로필 이름 작명 후 추가 버튼 클릭

2. 새 계정 버튼 클릭

3. 메일 계정 등록
    - 계정 이름 작명
    - 전자메일 주소 : 보내는 사람으로 표기될 메일 주소

    - 표시이름 : 보내는 사람의 이름
    - 회신 전자 메일 : 회신받을 주소

    - 서버이름 : 메일 서버 이름

    - 사용자이름 : 전자메일 주소의 계정명

    - 암호 : 전자메일 주소의 비밀번호

4. 마침

---

• Step 2-2-2. 프로시저 작성

BEGIN
    SET NOCOUNT ON
    
    DECLARE @RESULT INT
    
    SELECT @RESULT = COUNT(*)
    FROM 테이블
    WHERE 컬럼명 = 조건
    
    IF @RESULT > 0
    	BEGIN
			DECLARE @TO VARCHAR(100) = '받을 사람의 메일 주소'
			DECLARE @SUBJECT VARCHAR(100) = '제목'
			DECLARE @CONTENT VARCHAR(500) = '내용'
		
			EXEC MSDB.DBO.sp_send_dbmail
				@profile_name = 'test_sendMail',
				@recipients = @TO,
				@SUBJECT = @SUBJECT,
				@BODY = @CONTENT
     	END
END

 

- 이렇게 처리해 주면 @RESULT의 값이 0보다 크면 등록한 STMP를 통해 작성한 '받을 사람의 메일 주소'로 메일이 발송됨

 

 

===========================================================
틀린 내용이 있거나 이견 있으시면 언제든 가감 없이 말씀 부탁드립니다!
===========================================================