[MS-SQL] 프로시저(SP)로 PK값 생성 후 리턴 받는 방법 ( PK 값 자릿수 채우는 방법)

다음 프로시저는 데이터를 DB에 저장시 해당 테이블의 PK값이 자동증가하는 숫자가 아닌 고정값이 존재하고 자릿수를 채우는 PK값을 만드는 방법입니다.

스크립트를 실행하면 생성된 PK값을 가져옵니다.

SP_CREATE_PK라는 프로시저를 EXEC 명령어로 실행할 때 뒤에 OUTPUT를 정의해주고

SP_CREATE_PK 프로시저에서 리턴해주면 받아올 수 있습니다.

DECLARE	@P_ERROR_CD			VARCHAR(30)		= ''
DECLARE	@P_ROW_COUNT		INT				= 0
DECLARE	@P_RETURN_STR		NVARCHAR(100)	= ''
DECLARE @PK					VARCHAR(30)		= ''
			
EXEC SP_CREATE_PK 'SAVE', @P_ERROR_CD OUTPUT, @P_RETURN_STR OUTPUT; 
SET @PK = @P_RETURN_STR;

IF @P_ERROR_CD ='MSG0000'
	SELECT @PK
	--INSERT 작업하면 됨
ELSE 
	SELECT '오류 발생'

[실행결과]

PK 값 자릿수 채우는 방법

다음은 PK값을 생성하는 프로시저 (SP_CREATE_PK) 스크립트입니다. FN_MY_LPAD라는 스칼라 반환 함수를 생성하여 자릿수를 맞춥니다.

CREATE PROCEDURE SP_CREATE_PK (
	@P_TYPE			   VARCHAR(20),  
	@P_ERROR_CD		   VARCHAR(30) = ''    OUTPUT,     -- 에러코드 리턴  
	@P_RETURN_STR              NVARCHAR(100) = ''  OUTPUT      -- 반환되는 PK값
) 
AS 
SET NOCOUNT ON  
 
BEGIN 
   BEGIN TRY
	  
	DECLARE @P_DATE VARCHAR(8) = (CONVERT(VARCHAR(8), GETDATE(), 112));
	DECLARE @V_PREFIX VARCHAR(10) ='IDX';
	  
	-- 마지막 생성 번호
	DECLARE @V_LAST_NO INT = 3;	
		   
	SELECT @P_RETURN_STR = @V_PREFIX + dbo.FN_MY_LPAD(RTRIM(@V_LAST_NO + 1), 13 - LEN(@V_PREFIX), '0')

	SET @P_ERROR_CD = 'MSG0000'   --성공

   END TRY
   BEGIN CATCH
		DECLARE @V_EXEC_INFO NVARCHAR(MAX);

		-- 쿼리 실행 조건 기록을 위해
		SELECT @V_EXEC_INFO = N'SP_CREATE_PK '
				     + N', @@P_TYPE=N''' + ISNULL(@P_TYPE, '') + ''' '

		-- 프로시저 오류 발생시 에러 기록
		INSERT TB_PROCEDURE_ERR_LOG (ERR_PROC, ERR_CD, ERR_STA, ERR_MSG, EXEC_PROC_INFO)
		SELECT
			ERR_PROC = ERROR_PROCEDURE(),
			ERR_CD = CONVERT(VARCHAR(100), ERROR_NUMBER()),
			ERR_STA = ISNULL(SUBSTRING('Q_GET',1,1), 'X') + '|' + CONVERT(VARCHAR(100), ERROR_SEVERITY()) + '|' + CONVERT(VARCHAR(100), ERROR_STATE()) + '|' + CONVERT(VARCHAR(100), ERROR_LINE()),
			ERR_MSG = ERROR_MESSAGE(),
			EXEC_PROC_INFO = @V_EXEC_INFO
		;

		-- CRUD 에러코드 반환
		SELECT @P_ERROR_CD = (CASE SUBSTRING('Q_GET',1,1)
									WHEN 'Q' THEN 'ERR0006'				 -- 조회시 오류
									WHEN 'N' THEN 'ERR0008'				 -- 등록시 오류
									WHEN 'U' THEN 'ERR0009'				 -- 수정시 오류
									WHEN 'D' THEN 'ERR0010'				 -- 삭제시 오류
									ELSE 'ERR0000'
								END);
   END CATCH;
   ERROR_HANDLER:
END

[FN_MY_LPAD]

CREATE FUNCTION [dbo].[FN_MY_LPAD] (
	@s VARCHAR(255), 
	@n INT, 
	@p VARCHAR(1) 
) 
RETURNS VARCHAR(255)
AS
BEGIN
   RETURN REPLACE(SPACE(@n - DATALENGTH(@s)) + @s, ' ', @p)
END

프로시저를 실행시 오류가 발생할 것에 대비하여 테이블에 기록해두면 나중에 오류 발생시 아파치 톰켓 서버 로그를 보지 않더라도 프로시저에서 발생한 오류는 확인가능합니다.

로그 기록 테이블 스크립트는 다음과 같습니다.

CREATE TABLE [dbo].[TB_PROCEDURE_ERR_LOG](
	[IDN_SEQ] [bigint] IDENTITY(100000,1) NOT NULL,
	[ERR_PROC] [nvarchar](200) NULL,
	[ERR_CD] [varchar](100) NULL,
	[ERR_STA] [nvarchar](600) NULL,
	[ERR_MSG] [nvarchar](4000) NULL,
	[EXEC_PROC_INFO] [nvarchar](max) NULL,
	[CRT_TIME] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_TB_PROCEDURE_ERR_LOG] PRIMARY KEY NONCLUSTERED 
(
	[IDN_SEQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TB_PROCEDURE_ERR_LOG] ADD  DEFAULT (getdate()) FOR [CRT_TIME]
GO

이러한 방법으로 PK값을 생성하여 INSERT하는 경우 테이블 락이 걸리는 상황이 발생하는 경우가 생깁니다. 사용자가 동시에 접근하여 PK값을 생성하는 프로시저를 호출할 때 문제가 발생할 가능성이 있습니다.

그래서 테이블 생성시 PK값으로 숫자 자동증가 옵션(IDENTITY(100000,1)과 같이 쓰는 것이 좋다고 생각합니다.  생성되는 PK값으로 의미를 부여하고 식별할 필요가 있다면 위의 예제가 유용할 수 있으니 참고합니다.