DB 프로시저 묶기 INS(삽입), UPD(수정), DEL(삭제), SEL(전체), SEA(하나)

모듈화~! 실무에서는 어렇게 많이 쓴다

5개의 프로시저를 하나의 프로시저로 묶은것

18-06_JobsProcess.sql

USE Pubs

IF object_id('usp_JobsProcess') IS NOT NULL

  DROP PROC usp_JobsProcess

GO

CREATE PROC usp_JobsProcess -- 프로세스 묶었기때문에 이름정했다 

-- 아까와다른점 매개변수가 5개가있다

  @job_id   smallint = 0  

,  @job_desc varchar(50) = NULL  -- NOT NULL & DEFAULT(s)

, @min_lvl tinyint = NULL -- 디폴트 NULL

, @max_lvl tinyint = NULL -- 디폴트 NULL

, @prc_type char(3) = 'SEL' -- 임으로 만든것 prc_type 디폴트 'SEL' 

-- INS(삽입), UPD(수정), DEL(삭제), SEL(전체), SEA(하나)

AS 

  IF @prc_type = 'INS' -- 삽입 -- if 문

  BEGIN

 BEGIN TRAN

 DECLARE @ERR int -- 에러났을 에러값(정수)을 담는 변수

 DECLARE @ID smallint    -- @@IDENTITY 결과값 담을 변수

 /* 인자들로 일단 행 삽입(생략된 인자들도 포함). */

 INSERT INTO jobs (job_desc, min_lvl, max_lvl)

VALUES (@job_desc, @min_lvl, @max_lvl)

 SET @ERR = @@ERROR

 IF @ERR <> 0  GOTO ERROR

-- 에러가 없으면

 SET @ID = @@IDENTITY  -- 방금 사용된 IDENTITY 값을 보관

 /* 해당 열 값들을 열의 기본값으로 수정함 */

 IF @job_desc IS NULL -- job_desc 가 NULL 이면에러 BEGIN END

BEGIN

 UPDATE jobs

SET job_desc = DEFAULT

WHERE job_id = @ID

 SET @ERR = @@ERROR

 IF @ERR <> 0  GOTO ERROR

END

 COMMIT TRAN

 RETURN @ID  -- IDENTITY 값을 반환함

  END

  ELSE IF @prc_type = 'UPD' -- 수정 -- else if

  BEGIN

--  PRINT '수정'

 BEGIN TRAN

 IF @job_desc IS NOT NULL

BEGIN

 UPDATE jobs

SET job_desc = @job_desc

WHERE job_id = @job_id

 SET @ERR = @@ERROR

 IF @ERR <> 0  GOTO ERROR

END

 IF @min_lvl IS NOT NULL

BEGIN

 UPDATE jobs

SET min_lvl = @min_lvl

WHERE job_id = @job_id

 SET @ERR = @@ERROR

 IF @ERR <> 0  GOTO ERROR

END

 IF @max_lvl IS NOT NULL

BEGIN

 UPDATE jobs

SET max_lvl = @max_lvl

WHERE job_id = @job_id

 SET @ERR = @@ERROR

 IF @ERR <> 0  GOTO ERROR

END

 COMMIT TRAN

 RETURN

  END

  ELSE IF @prc_type = 'DEL' -- 삭제  -- else if

  BEGIN

 BEGIN TRAN

 DELETE jobs

WHERE job_id = @job_id

 SET @ERR = @@ERROR

 IF @ERR <> 0  GOTO ERROR

 COMMIT TRAN

 RETURN

  END

  ELSE IF @prc_type = 'SEL' -- 전체 셀렉트  -- else if

  BEGIN

 SELECT *

FROM jobs

ORDER BY job_id

-- 셀렉트문은 

RETURN -- BEGIN TRAN이 없기때문에 리턴해야한다

  END

  ELSE IF @prc_type = 'SEA' -- 부분 셀렉트  -- else if

  BEGIN  

 SELECT *

FROM jobs

WHERE job_id = @job_id

     RETURN -- BEGIN TRAN이 없기때문에 리턴해야한다

  END

ERROR:

  DECLARE @error_msg nvarchar(400)

  SET @error_msg = dbo.sfn_MakeErrMsg('usp_JobsProcess', @ERR, DEFAULT)

  RAISERROR( @error_msg, 16, 1 )

  ROLLBACK TRAN

GO

모듈화~! 실무에서는 어렇게 많이 쓴다

EXEC usp_jobsProcess DEFAULT ,'System Admin' ,20, 150 , 'INS'

EXEC usp_jobsProcess 28 ,'System Enginer' ,20, 150 , 'UPD'

EXEC usp_jobsProcess 17 ,DEFAULT ,DEFAULT, DEFAULT , 'DEL'

EXEC usp_jobsProcess DEFAULT ,DEFAULT ,DEFAULT, DEFAULT , 'SEL'

EXEC usp_jobsProcess 28 ,DEFAULT ,DEFAULT, DEFAULT , 'SEA'

SELECT * FROM jobs ORDER BY job_id DESC