SQLServer

MS-SQL 서버는 프로시져 네이밍 규칙이 다음과 같다.

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

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;

  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;
  

  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;