[MSSQL] 프로시저 기본 포맷

프로시저 생성 기본 포맷 샘플

• 기본 IN/OUT 파라미터
• 주석 포맷
• 변수 선언
• 로그 기록 TRY CATCH 등등

 

CREATE PROCEDURE BATCH.SP_COMMON
     @P_SEQ_ID      VARCHAR(32)
    ,@P_WK_DT       VARCHAR(8)
    ,@O_RST         VARCHAR(2)      OUTPUT
    ,@O_RST_MSG     NVARCHAR(4000)  OUTPUT
AS 
/*****************************************************************************
 설명
 
 @AUTHOR    : 
 @DATE      : 
 @PARAM     : 
 

DECLARE 
     @P_SEQ_ID      VARCHAR(32) = 'P'
    ,@P_WK_DT       VARCHAR(8) = COM.TO_CHAR(GETDATE()-2, 'YYYYMMDD')
    ,@O_RST         VARCHAR(2)
    ,@O_RST_MSG     NVARCHAR(4000);

EXEC BATCH.SP_COMMON  @P_SEQ_ID,@P_WK_DT, @O_RST OUT,@O_RST_MSG OUT;
SELECT @P_SEQ_ID,@P_WK_DT,@O_RST,@O_RST_MSG;

 *******************************************************************************/
BEGIN
    SET NOCOUNT ON;

    -- VARIABLES BATCH_LOG
    DECLARE  @VL_PGM_ID         VARCHAR(40) = 'BATCH.SP_COMMON'
            ,@VL_LOG_CNT        INT
            ,@VLB_LOG_DESC      NVARCHAR(4000)
            ;
    -- VARIABLES 
    DECLARE  @V_FRDT            VARCHAR(8)  = CONVERT(VARCHAR, GETDATE()-2, 112)
            ,@V_TODT            VARCHAR(8)  = CONVERT(VARCHAR, GETDATE(), 112)
            ,@V_TODTTM          DATETIME
            ,@V_API_SEQ_NO      FLOAT
            ,@V_JSON_STR        NVARCHAR(MAX)
            ;
    
    -- ==========================================================================================
    -- START_LOG(S)
    SELECT
          @O_RST = 'R'
        , @VLB_LOG_DESC = 'START'
        , @VL_LOG_CNT = 0
    ;
    -- 로그 기록 프로시저
    EXEC BATCH.SP_BATCH_LOG @VL_PGM_ID, @VLB_LOG_DESC, @VL_LOG_CNT;
    
    -- START_LOG(E)
    -- ==========================================================================================

    BEGIN TRY
        IF @P_WK_DT IS NOT NULL AND @P_WK_DT > GETDATE()-1
            BEGIN 
                SELECT @O_RST = 'F', @O_RST_MSG = '파라미터 오류';
                GOTO PROC_USER_ERROR;
            END
            
        SET @V_FRDT = ISNULL(@P_WK_DT, @V_FRDT);
    
        SELECT 
              @VLB_LOG_DESC = CONCAT('START_',@V_FRDT)
             ,@O_RST = 'F'
        ;
        EXEC BATCH.SP_BATCH_LOG @VL_PGM_ID, @VLB_LOG_DESC, @VL_LOG_CNT;
    
        -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
        -- LOGIC_START
         
        BEGIN TRAN
        
        -- 1.  ---------------------------------------------------------------------------------

        -- INSERT ....

        SET @VL_LOG_CNT = @@ROWCOUNT;

        IF @VL_LOG_CNT > 0
          BEGIN
            SELECT @O_RST_MSG = CONCAT('성공 : ', @O_RST_MSG);
          END
        ELSE
          BEGIN
            SELECT @O_RST = 'F', @O_RST_MSG = CONCAT(@O_RST_MSG, ', STEP 1 실패');
            GOTO PROC_USER_ERROR;
          END
        
        EXEC BATCH.SP_BATCH_LOG @VL_PGM_ID, @VLB_LOG_DESC, @VL_LOG_CNT;

        -- 2.  ---------------------------------------------------------------------------------
        
        -- 


        COMMIT TRAN;

        -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
        -- LOGIC_END 
    
        PROC_USER_ERROR:
        BEGIN
           IF @@TRANCOUNT = 1
               ROLLBACK TRAN;
           ELSE IF @@TRANCOUNT > 1
               COMMIT TRAN;
            
            EXEC BATCH.SP_BATCH_LOG @VL_PGM_ID, N'PROC_USER_ERROR', NULL;
        
        END; --PROC_USER_ERROR
      
        -- ==========================================================================================
        -- FINISH_LOG
        END_PROC:
            EXEC BATCH.SP_BATCH_LOG @VL_PGM_ID,N'END', NULL;

    END TRY
    
    BEGIN CATCH
--      PRINT 'CATCH';

        SELECT 
              @O_RST = 'F'
            , @O_RST_MSG = CONCAT(CONVERT(VARCHAR(10),ERROR_NUMBER()) , ':' , ERROR_MESSAGE() )
        ;
        EXEC BATCH.SP_BATCH_LOG @VL_PGM_ID, N'END', NULL;
        
    END CATCH
    -- FINISH_LOG(END) 
    -- ==========================================================================================
END;