[Sequel Safe] SP 작성 규칙

1. 주석 작성 규칙
1. 상단 주석 작성 방법
1. 작성자, 작성일자, 내용, 리턴값을 기입합니다.
2. 모든 SP는 int 타입의 값을 리턴합니다.
   만약, 정의하지 않는다면 암묵적으로 0이 반환되는데... 이 값을 SP의 실행 결과 또는 에러 번호로 활용하는 것이 좋습니다.
3. description 항목은 SP가 CREATE될 때, DDL트리거에 의해 확장 속성으로 자동 추가됩니다.
   
   
   /**

   author : doeyull.kim

   e-mail : purumae@gmail.com

   created date : 2009-03-31

   description : 기사카테고리를추가합니다.

   return value :

   0 = 에러가없습니다.

   1 = 트랜잭션을Commit할수없는상태입니다. 트랜잭션을Rollback합니다.

   100 = 시스템에러가발생하였습니다. dbo.ErrorLogs 테이블을조회하세요.
   101 = 상위기사카테고리가존재하지않습니다.
   

   **/
   

   
   
2. 파라미터 주석 작성 방법
1. 파라미터 오른쪽에 --// 로 시작하는 한 줄 주석을 작성합니다.
2. 작성된 주석은 SP가 CREATE될 때, DDL트리거에 의해 확장 속성으로 자동 추가됩니다.
   
   
         @intParentCategoryID int = NULL --// 상위기사카테고리일련번호.

       , @nvcCategoryName nvarchar(100) --// 기사카테고리이름.

       , @intCategoryID int = NULL OUTPUT --// 기사카테고리일련번호.

   
   
3. 로직 flow를 설명하기 위한 주석 작성 방법
1. sp_helpmodule을 통해 출력되는 문서에는 Flow블럭이 있는데, 이 곳에 출력하기 위한 주석 작성 규칙입니다.
2. /**_ 로 시작해서 */로 끝 맺습니다.
3. /**_ 바로 뒤에 #을 사용하여 번호를 매기게 됩니다 (필수).
   번호를 들여쓸때는, 들여쓰는 depth만큼 #을 반복합니다.
   
   

   /**_# Rollback and return if inside an uncommittable transaction.*/

   /**_# 회원정보를쿼리합니다.*/

   /**_## 회원정보가존재하지않으면4010105를리턴합니다.*/

   /**_# 일반회원계정이아니면, 4010152를리턴합니다.*/

   /**_## 14세미만내국인회원가입신청이후보호자동의가완료되지않은상태이면4010112를리턴합니다.*/

   /**_## 외국인회원가입신청이후여권확인이완료되지않은상태이면4010120를리턴합니다.*/

   /**_## 사용자추가가제한된회원이면, 4010122를리턴합니다.*/

   /**_# 회원이가진사용자ID 개수를쿼리하여, 현재3개이상이라면4010111을리턴합니다.*/

   /**_# 제재된회원이면, 4010153을리턴합니다.*/

   /**_# User를추가합니다.*/

   /**_## [Users] 테이블에INSERT합니다.*/

   /**_### 이미존재하는User ID이면4010106을리턴합니다.*/

   /**_## [UserInformations] 테이블에INSERT합니다.*/

   /**_### 이미존재하는닉네임이면4010110을리턴합니다.*/

   /**_### 이미등록된이메일이면4010121을리턴합니다.*/

   /**_# 가입약관동의를기록합니다.*/

   /**_# 메일수신에동의한경우메일링리스트에추가합니다.*/

   /**_## [MailingLists] 테이블에INSERT합니다.*/

   /**_## [MailingListLogs] 테이블에INSERT합니다.*/

   
   
   
   소스 중간 중간에 위와 같이 주석을 작성했다면, sp_helpmodule에 의해 아래와 같이 출력됩니다.
   
   

   1. Rollback and return if inside an uncommittable transaction.

   2. 회원정보를쿼리합니다.

       1. 회원정보가존재하지않으면4010105를리턴합니다.

   3. 일반회원계정이아니면, 4010152를리턴합니다.

       1. 14세미만내국인회원가입신청이후보호자동의가완료되지않은상태이면4010112를리턴합니다.

       2. 외국인회원가입신청이후여권확인이완료되지않은상태이면4010120를리턴합니다.

       3. 사용자추가가제한된회원이면, 4010122를리턴합니다.

   4. 회원이가진사용자ID 개수를쿼리하여, 현재3개이상이라면4010111을리턴합니다.

   5. 제재된회원이면, 4010153을리턴합니다.

   6. User를추가합니다.

       1. [Users] 테이블에INSERT합니다.

           1. 이미존재하는User ID이면4010106을리턴합니다.

       2. [UserInformations] 테이블에INSERT합니다.

           1. 이미존재하는닉네임이면4010110을리턴합니다.

           2. 이미등록된이메일이면4010121을리턴합니다.

   7. 가입약관동의를기록합니다.

   8. 메일수신에동의한경우메일링리스트에추가합니다.

       1. [MailingLists] 테이블에INSERT합니다.

       2. [MailingListLogs] 테이블에INSERT합니다.

   
   
2. EXECUTE AS 'SPExecutor'
1. SP가 실행될 때의 보안 컨텍스트를 SPExecutor로 강제 지정합니다.
2. Sequel Safe를 설치하면, SPExecutor라는 Login 및 User가 추가됩니다. (db_owner 역할입니다.)
3. Application Server가 데이터베이스에 연결할 때 사용하는 Login의 권한은 매우 제한적인데, Sequel Safe에서는 모든 SP에 대한 Execute 권한 뿐입니다. 문제는 SP에서 동적 Query를 사용하거나 DDL문을 사용할 때 발생하는데, 이런 제약을 피하기 위해 db_owner 역할에 속하는 SPExecutor 사용자를 "가장"하도록 합니다.
   
   
   WITH EXECUTE AS 'SPExecutor'
   
   
   
3. 실행 환경 설정 (SET 옵션)
1. SET NOCOUNT ON
   affected row의 수를 클라이언트에 전달하지 않는 옵션입니다.
   성능에 큰 영향이 있으므로, 반드시 설정하셔야 합니다.
   
   

   SET NOCOUNT ON;

   
   
2. SET XACT_ABORT ON
   SP 실행 중 run time 에러가 발생하면, 트랜잭션을 롤백하는 옵션입니다.
   에러 핸들링 정책과 밀접한 관계가 있으며, Sequel Safe이 제시하는 에러 핸들링 방법을 사용하려면 이 옵션도 필수 입니다.
   
   
   SET XACT_ABORT ON;
   
   
4. 항상 선언하는 변수 @intReturnValue
1. SP의 리턴 값을 저장하기 위한 변수로, 모든 SP에서 맨 처음 선언하는 변수입니다.
   
   
   DECLARE @intReturnValue int;
   
   
   
5. 변수 명명 규칙
1. 헝거리안 표기법을 사용합니다.
   
   

   데이터 타입	접두사	예
   char	chr	@chrSSN
   varchar	vch	@vchTitle
   nchar	nch	@nchName
   nvarchar	nvc	@nvcSubject
   datetime	dtm	@dtmIssuedDate
   smalldatetime	sdt	@sdtStartDate
   tinyint	iny	@inyBankCode
   smallint	ins	@insArticleCount
   int	int	@intCustomerSN
   bigint	inb	@inbCyberCashAmount
   numeric / decimal	dec	@decProfit
   money	mny	@mnyRealCashAmount
   binary	bin	@binPath
   varbinary	vbi	@vbiPassword
   bit	bit	@bitCompletedFlag
   timestamp	tsp	@tspRowVersion
   uniqueidentifier	guid	@guidSubscriptionKey
   xml	xml	@xmlPart
   table	tbl	@tblNumbers
   date	dat	@datLogDate
   datetime2	dt2	@dt2BeginDate
   datetimeoffset	dto	@dtoBasisDate
   time	tim	@timLoginTime
   geography	geg	@gegLocation
   geometry	gem	@gemLocation
   hierarchyid	hid	@hidBBSID

   
   
6. Orphaned Transaction 처리를 위한 구문
1. orphaned transaction 이란... 트랜잭션이 commit도 rollback도 되지 못한 상태로 있게 되는 매우 비 정상적인 상황입니다.
   XACT_ABORT 옵션이 켜져 있어도, 207이나 208에러와 같이 자동 롤백이 되지 않는 종류의 에러가 발생하면 orphaned transaction이 생길 수 있는데... 이런 상황이 되면 블럭킹으로 시스템 장애가 올 수 있기 때문에 이에 대비한 루틴이 모든 SP의 상단에 필요합니다.
2. XACT_STATE() 함수가 반환하는 값이 -1인 경우
   ☞ 트랜잭션이 열려있으나 commit 불가능한 상태임을 의미합니다.
   
   
   /**_# Rollback and return if inside an uncommittable transaction.*/

   IF XACT_STATE() = -1

   BEGIN

       SET @intReturnValue = 1;

       GOTO ErrorHandler;

   END

   
   
7. TRY ~ CATCH ~ 블럭
1. 로직을 수행하기 위한 구문은 TRY 블럭에 작성합니다.
2. TRY 블럭에서 run time 에러가 발생하면 CATCH 블럭의 구문이 실행됩니다. 그런데 이 에러를 잘 활용하면 우리는 그 중에서 예상 가능한 Logical한 에러를 골라 SP의 리턴값을 매핑할 수 있습니다.
   
   아래 예에서 Categories 테이블에 INSERT할 때 예상 가능한 에러는, 부모 카테고리 ID가 존재하지 않는 경우를 생각해 볼 수 있습니다.
   
   권고하는데, TRY 블럭에서 IF EXISTS()문으로 부모 카테고리 ID의 존재 여부를 확인하지 마십시오.
   그 대신... 샘플에서처럼 FOREIGN KEY를 설정해 두고, SP에서는 바로 INSERT 하십시오.
   
   부모 카테고리 ID가 존재한다면 에러 없이 INSERT가 수행될 것이고, 그렇지 않다면 547번 에러가 발생하여 CATCH 블럭이 실행될 것입니다.
   
   우리는 CATCH 블럭을 작성할 때, 위와 같이 예상 가능한 에러에 대해 핸들링 할 수 있습니다.
   
   즉, 에러번호가 547이고 위반한 Constraint의 이름이 FK_Categories_Categories 문자열을 포함한다면 SP가 101번을 리턴하도록 하는 것이죠.
   
   
   BEGIN TRY

       /**_# [Categories] 테이블에INSERT합니다.*/
       INSERT dbo.Categories (topCategoryID, parentCategoryID, categoryName)

       SELECT NULL, @intParentCategoryID, @nvcCategoryName, @intCategoryLevel

       FROM dbo.Categories WITH (NOLOCK)

       WHERE categoryLevel = @intCategoryLevel;

    

       SET @intCategoryID = SCOPE_IDENTITY();

       COMMIT TRANSACTION;

   END TRY

   BEGIN CATCH

       IF ERROR_NUMBER() = 547 AND ERROR_MESSAGE() LIKE N'%FK_Categories_Categories%'

           SET @intReturnValue = 101;

    

       GOTO ErrorHandler;

   END CATCH;




8. ErrorHandler 레이블
1. 보통은 CATCH 블럭에 의해 ErrorHandler 레이블로 이동할 것이지만, TRY 블럭에서 개발자가 직접 ErrorHandler 레이블로 GOTO 할 수 있습니다.
2. ErrorHandler 레이블은 @intReturnValue에 값이 정의 된 경우.. 그 값을 리턴하고 정상 종료하게 합니다.
   하지만, 예상하지 못한 시스템 에러가 발생할 때는 @intReturnValue의 값이 0이거나 NULL인 상황이 만들어 지는데... 이 경우 dbo.ErrorLogs 테이블에 에러 내용을 INSERT하고 에러를 Rethrow하게 됩니다.
3. 아래 소스에 등장하는 dbo.P_AddErrorLog는 지난 포스트에서 말씀드린 공통 모듈 중 하나입니다.
   앞 선 포스트의 내용대로 따로 오셨다면, 이미 테스트 DB에 존재하고 있습니다.
   
   

   ErrorHandler:

   IF XACT_STATE() <> 0

       ROLLBACK TRANSACTION;

    

   IF @intReturnValue IS NULL OR @intReturnValue = 0

       EXEC @intReturnValue = dbo.P_AddErrorLog;

    

   RETURN @intReturnValue;
   

   
   
   
9. 기타
1. INSERT 절에서 INTO를 생략합니다.
2. DELETE 바로 다음에 나오는 FROM을 생략합니다.