Carpe diem

-- 프로시저 호출할때 사용할 변수

DECLARE @OPTIONS VARCHAR(MAX)

SET @OPTIONS = 'O|id1|sub_title1|img_path1|cnt1|order1|O|id2|sub_title2|img_path2|cnt2|order2'

-- 프로시저 내에서 사용할 변수

DECLARE @SPLIT_TXT CHAR(1) = '|' (= 구분자)

DECLARE @POS INT

DECLARE @OPTION_OR_DETAIL CHAR(1)

DECLARE @OPTION_ID INT

DECLARE @OPTION_NAME VARCHAR(500)

DECLARE @OPTION_IMG_PATH VARCHAR(1000)

DECLARE @OPTION_CNT INT

DECLARE @OPTION_ORDER INT

BEGIN

  SET @OPTIONS = LTRIM(RTRIM(@OPTIONS)) + @SPLIT_TXT

 = "@OPTIONS" 뒤에 구분자 추가

  SET @POS = CHARINDEX(@SPLIT_TXT, @OPTIONS, N)

 = N번째 이후로 "@OPTIONS" 에서 "@SPLIT_TXT" 가 있는 위치 반환

 = JAVA 의 indexOf() 와 같은 기능

  IF REPLACE(@OPTIONS, @SPLIT_TXT, '') <> ''

    BEGIN

      WHILE @POS > 0

        BEGIN

          SET @OPTION_OR_DETAIL = LTRIM(RTRIM(LEFT(@OPTIONS, @POS - 1)))

= O|id1|sub_title1|img_path1|cnt1|order1

= JAVA 의 substring() 과 같은 기능

          SET @OPTIONS = RIGHT(@OPTIONS, LEN(@OPTIONS) - @POS)

= 앞에서 사용한 문자열 이후로 다시 셋

          SET @POS = CHARINDEX(@SPLIT_TXT, @OPTIONS, 1)

= N번째 이후로 "@OPTIONS" 에서 "@SPLIT_TXT" 가 있는 위치 반환

= JAVA 의 indexOf() 와 같은 기능

.....반복.....

          SET @OPTION_ID = LTRIM(RTRIM(LEFT(@OPTIONS, @POS - 1)))

= O|id1|sub_title1|img_path1|cnt1|order1

          SET @OPTIONS = RIGHT(@OPTIONS, LEN(@OPTIONS) - @POS)

          SET @POS = CHARINDEX(@SPLIT_TXT, @OPTIONS, 1)

          SET @OPTION_NAME = LTRIM(RTRIM(LEFT(@OPTIONS, @POS - 1)))

= O|id1|sub_title1|img_path1|cnt1|order1

          SET @OPTIONS = RIGHT(@OPTIONS, LEN(@OPTIONS) - @POS)

          SET @POS = CHARINDEX(@SPLIT_TXT, @OPTIONS, 1)

          SET @OPTION_IMG_PATH = LTRIM(RTRIM(LEFT(@OPTIONS, @POS - 1)))

= O|id1|sub_title1|img_path1|cnt1|order1

          SET @OPTIONS = RIGHT(@OPTIONS, LEN(@OPTIONS) - @POS)

          SET @POS = CHARINDEX(@SPLIT_TXT, @OPTIONS, 1)

          SET @OPTION_CNT = LTRIM(RTRIM(LEFT(@OPTIONS, @POS - 1)))

= O|id1|sub_title1|img_path1|cnt1|order1

          SET @OPTIONS = RIGHT(@OPTIONS, LEN(@OPTIONS) - @POS)

          SET @POS = CHARINDEX(@SPLIT_TXT, @OPTIONS, 1)

          SET @OPTION_ORDER = LTRIM(RTRIM(LEFT(@OPTIONS, @POS - 1)))

= O|id1|sub_title1|img_path1|cnt1|order1

          IF( @OPTION_ID = 0 )

            BEGIN            

              --INSERT 구문

              SET @OPTION_ID = SCOPE_IDENTITY()

 = Insert 실행 후 Key 값 리턴

            END

          ELSE

            BEGIN

              --업데이트 구문

            END

          SET @OPTIONS = RIGHT(@OPTIONS, LEN(@OPTIONS) - @POS)

          SET @POS = CHARINDEX(@SPLIT_TXT, @OPTIONS, 1)

        END -- IN OF WHILE

    END -- OUT OF WHILE

[COMMIT]

CREATE PROCEDURE TEST_DELETE_PROC

@TYPE_ID      INT,

@RTN_CODE     INT = 0  OUTPUT,

@RTN_MSG     VARCHAR(300)  OUTPUT

AS    

SET NOCOUNT ON

BEGIN TRAN

DELETE

  FROM dbo.TB_TEST

WHERE TYPE_ID = @TYPE_ID

IF (@@ERROR <> 0) BEGIN GOTO ERRHANDLE END

GOTO OKHANDLE

---===================================

-- ERROR HANDLE AREA

---===================================

OKHANDLE:

SET @RTN_CODE = 0

SET @RTN_MSG = '정상 처리'

COMMIT TRAN

RETURN (0)

ERRHANDLE:

SET @RTN_CODE = 3

SET @RTN_MSG = '오류 발생'

ROLLBACK TRAN

RETURN (3)

;

DECLARE @RTN_CODE int, @RTN_MSG VARCHAR(300)

EXEC TEST_DELETE_PROC 1, @RTN_CODE OUTPUT, @RTN_MSG OUTPUT

SELECT @RTN_CODE, @RTN_MSG

CREATE PROCEDURE TEST_INSERT_PROC

@TYPE_NAME      VARCHAR(100),

@RTN_CODE     INT = 0  OUTPUT,

@RTN_MSG     VARCHAR(300)  OUTPUT

AS

SET NOCOUNT ON

BEGIN TRAN

INSERT INTO dbo.TB_TEST (TYPE_NAME)

VALUES (@TYPE_NAME)

IF (@@ERROR <> 0) BEGIN GOTO ERRHANDLE END

GOTO OKHANDLE

---===================================

-- ERROR HANDLE AREA

---===================================

OKHANDLE:

SET @RTN_CODE = 0

SET @RTN_MSG = '정상처리'

COMMIT TRAN

RETURN (0)

ERRHANDLE:

SET @RTN_CODE = 3

SET @RTN_MSG = '오류 발생'

ROLLBACK TRAN

RETURN (1)

DECLARE @RTN_CODE int, @RTN_MSG VARCHAR(300)

EXEC TEST_INSERT_PROC 'INSERT 테스트', @RTN_CODE OUTPUT, @RTN_MSG OUTPUT

SELECT @RTN_CODE, @RTN_MSG

CREATE TABLE dbo.TB_TEST (

    TYPE_ID    INT IDENTITY(1,1) NOT NULL, 

    TYPE_NAME     VARCHAR(100)

 CONSTRAINT PK_TB_TEST 

   PRIMARY KEY CLUSTERED (TYPE_ID ASC) ON [PRIMARY]

) ON [PRIMARY]