USE Pubs
IF object_id('usp_JobsProcess') IS NOT NULL
  DROP PROC usp_JobsProcess
GO

CREATE PROC usp_JobsProcess 
  @job_id   smallint = 0   --> IDENTITY ¿­À̹ǷΠ»ç¿ë ºÒ°¡
,  @job_desc	varchar(50) = NULL  -- NOT NULL & DEFAULT(s)
, @min_lvl	tinyint = NULL
, @max_lvl	tinyint = NULL
, @prc_type	char(3) = 'SEL'
	-- INS(»ðÀÔ), UPD(¼öÁ¤), DEL(»èÁ¦), SEL(Àüü), SEA(Çϳª)
AS 
  IF @prc_type = 'INS' -- »ðÀÔ
  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
		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' -- ¼öÁ¤	
  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' -- »èÁ¦
  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' -- Àüü ¼¿·ºÆ®
  BEGIN
	  SELECT *
		FROM jobs
		ORDER BY job_id

	 RETURN
  END
  ELSE IF @prc_type = 'SEA' -- ºÎºÐ ¼¿·ºÆ®
  BEGIN
	  SELECT *
		FROM jobs
		WHERE job_id = @job_id
     RETURN
  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