====== SQLServer ======

MS-SQL 서버는 프로시져 네이밍 규칙이 다음과 같다.\\
   기존예시 : 
   MARIA  경우 =>PKG_SYS$GET_FAVORITY
   ORACLE 경우 =>PKG_SYS.GET_FAVORITY <- *패키지 사용
   
   MSSQL(소문자) => pkg_sys_get_favority
      
   MS-SQL SERVER DBHelper에서는 '$', '.' => '_' 로 변환되어 호출한다.

===== Select =====

	ALTER   PROCEDURE [dbo].[pkg_sys_get_favorite]
	    @A_PLANT NVARCHAR(10),
	    @A_LANG NVARCHAR(10),
	    @A_USER_ID BIGINT,
	    @N_RETURN INT OUTPUT,
	    @V_RETURN NVARCHAR(4000) OUTPUT
	AS
	BEGIN
	    SET NOCOUNT ON;
	    DECLARE @li_seq NVARCHAR(3);
            
	    BEGIN TRY	 
            
		select A.MENU_ID MENUSEQ,
			A.MENU_ID,
			C.CODE FORM,
			C.CODE,
			C.NAME,
			C.A_ADD,
			C.A_DELETE ,
			C.A_SAVE ,
			C.A_PRINT ,
			B.PATH ,
			A.DISP_SN
		from SYS_FAVORITE A
		inner join SYS_MENU B on A.MENU_ID = B.MENU_ID
		inner join SYS_PROG C on C.PROG_ID = B.PROG_ID 
		where A.USER_ID = @A_USER_ID
			and A.DELETE_YN = 'N'
			and C.USE_YN = 'Y'
		order by A.DISP_SN;
	    	
		SET @N_RETURN = 0;
	    SET @V_RETURN = '저장되었습니다.';
	   
            
	    END TRY
	    BEGIN CATCH
		SET @N_RETURN = -1;
		SET @V_RETURN = ERROR_MESSAGE();
	    END CATCH
	END;

==== Insert ====

    ALTER   PROCEDURE [dbo].[pkg_sys_set_file_insert]
        @A_COMP_ID NVARCHAR(20),
        @A_TABLE_NAME NVARCHAR(100),
        @A_TABLE_KEY NVARCHAR(50),
        @A_TABLE_GUBUN NVARCHAR(2),
        @A_SEQ DECIMAL(10,0),
        @A_FILE_NAME NVARCHAR(200),
        @A_REAL_FILE_NAME NVARCHAR(200),
        @A_FILE_SIZE INT,
        @A_RMK NVARCHAR(50),
        @A_EMP_NO NVARCHAR(10),
        @N_RETURN INT OUTPUT,
        @V_RETURN NVARCHAR(4000) OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON;
    
      BEGIN TRY
    
     
      SET @N_RETURN = 0;
      SET @V_RETURN = '저장 성공';
            INSERT INTO TC_ERP_FILE_MANAGE
                (COMP_ID, 
                 TABLE_NAME, 
                 TABLE_KEY, 
                 TABLE_GUBUN, 
                 SEQ, 
                 FILE_NAME, 
                 REAL_FILE_NAME, 
                 FILE_SIZE, 
                 AUTH_YN, 
                 REG_GUBUN, 
                 EMP_NO, 
                 RMK, 
                 SYS_EMP_NO, 
                 SYS_DATE, 
                 UPD_EMP_NO, 
                 UPD_DATE 
                 )
            VALUES
                (@A_COMP_ID,
                 @A_TABLE_NAME,
                 @A_TABLE_KEY,
                 @A_TABLE_GUBUN,
                 @A_SEQ,
                 @A_FILE_NAME,
                 @A_REAL_FILE_NAME,
                 @A_FILE_SIZE,
                 '',
                 '',
                 @A_EMP_NO,
                 @A_RMK,
                 @A_EMP_NO,
                 SYSDATETIME(),
                 @A_EMP_NO,
                 SYSDATETIME());
    	IF (@@ROWCOUNT < 1) BEGIN
    		RETURN;
    END;
              
    
        END TRY
        BEGIN CATCH
            SET @N_RETURN = -1;
            SET @V_RETURN = ERROR_MESSAGE();
        END CATCH
    END;
    
=== Update ===
    ALTER   PROCEDURE [dbo].[pkg_sys_set_file_update]
        @A_COMP_ID NVARCHAR(20),    
        @A_TABLE_NAME NVARCHAR(100),
        @A_TABLE_KEY NVARCHAR(20),
        @A_TABLE_GUBUN NVARCHAR(2),
        @A_SEQ DECIMAL(10,0),
        @A_FILE_NAME NVARCHAR(200),
        @A_REAL_FILE_NAME NVARCHAR(200),
        @A_FILE_SIZE INT,
        @A_RMK NVARCHAR(50),
        @A_EMP_NO NVARCHAR(10),
        @N_RETURN INT OUTPUT,
        @V_RETURN NVARCHAR(4000) OUTPUT
    AS
    BEGIN
        SET NOCOUNT ON;
    
        BEGIN TRY
     
           SET @N_RETURN = 0;
           SET @V_RETURN = '저장 성공';
           UPDATE TC_ERP_FILE_MANAGE
               SET FILE_NAME      = @A_FILE_NAME, 
                   REAL_FILE_NAME = @A_REAL_FILE_NAME, 
                   FILE_SIZE      = @A_FILE_SIZE, 
                   RMK            = @A_RMK, 
                   UPD_EMP_NO     = @A_EMP_NO, 
                   UPD_DATE       = SYSDATETIME() 
             WHERE COMP_ID = @A_COMP_ID
               AND TABLE_NAME = @A_TABLE_NAME
               AND TABLE_KEY = @A_TABLE_KEY
               AND TABLE_GUBUN = @A_TABLE_GUBUN
               AND SEQ = @A_SEQ;
    	   IF (@@ROWCOUNT < 1) 
           BEGIN
    		RETURN;
           END;      
    
        END TRY
        BEGIN CATCH
            SET @N_RETURN = -1;
            SET @V_RETURN = ERROR_MESSAGE();
        END CATCH
    END;