MSSQL 저장 프로시저 디버깅 방법

MSSQL에서 저장 프로시저를 디버깅하는 방법을 너무 힘들어하는 것 같아서 stored procedure 간단하게 디버깅하는 방법에 관해서 설명하려고 합니다.

저장 프로시저를 디버깅 종류

PRINT문 이용 프로시저 디버깅 방법은 값을 확인할 때 가장 많이 사용하는 방법으로 필요한 포인트에 print문을 사용하여 값을 확인하는 방법입니다.
SELECT 문 사용에서 프로시저 디버깅 방법은 보통 dynamic query 사용할 때 사용하는데 excute 되는 문장하고 파라미터를 출력할 때 사용합니다.
RAISERROR은 특정 지점에 에러 메시지를 보여주면서 값을 확인할 수 있습니다.
DEBUG FLAG는 프로시저에 파라미터 선언할 때 추가한 후 실행할 때 DEBUG FLAG 변경하면서 디버깅하는 방법입니다.

저장 프로시저를 디버깅 방법 및 예제

PRINT문 이용 프로시저 디버깅 방법.

PRINT 출력한 내용은 Messages 확인할 수 있습니다.
PRINT문 소스 예시입니다.

CREATE TABLE UserInfoForDebugging (
    UserId int,
    UserNm varchar(255) NOT NULL, 
    UserAge int
);
GO 

INSERT INTO dbo.UserInfoForDebugging(UserId,UserNm,UserAge) 
VALUES (272, N'Ken',10)
      ,(273, N'Brian',34)
      ,(274, N'Debugging',45)
      ,(275, N'Michael',65)
      ,(276, N'Linda',12);
GO
 
-- PRINT문 이용 프로시저 디버깅 방법. 
-- DROP PROCEDURE [dbo].[STORE_PROCEDURE_DEBUGGING_PRINT]
CREATE PROCEDURE [dbo].[STORE_PROCEDURE_DEBUGGING_PRINT]   
    @UserNm NVARCHAR(10) 
AS
BEGIN
    PRINT('userNmValue:' +  @UserNm);
    SELECT * 
	FROM UserInfoForDebugging 
	WHERE UserNm = @UserNm	  
	;
END;

GO
--  소스 실행
EXECUTE DBO.[STORE_PROCEDURE_DEBUGGING_PRINT] 'Debugging';

SELECT 문 사용에서 프로시저 디버깅 방법.

SELECT 문은 dynamic query 사용할 때 실행되는 query를 확인합니다.
저장 프로시저 소스가 Insert 문이라고 하면, 실행문은 주석 처리하고 SELECT 절만 넣어서 확인할 수 있습니다.
SELECT 문 소스 예시입니다.

-- SELECT문 사용해서 프로시저 디버깅 방법.
-- DROP PROCEDURE [dbo].[STORE_PROCEDURE_DEBUGGING_SELECT];
CREATE PROCEDURE [dbo].[STORE_PROCEDURE_DEBUGGING_SELECT] 
  @ParamUserId    INT
 ,@ParamUserNm    VARCHAR(100)
 ,@ParamUserAge   INT 
 
AS  
BEGIN 
	DECLARE @InsertString NVARCHAR(500);
	DECLARE @ParmDefinition NVARCHAR(500);  
  
	SET @InsertString = ' INSERT INTO  dbo.UserInfoForDebugging ' 
					+ ' ( UserId,UserNm,UserAge ) ';
 						 
	IF(@ParamUserAge > 20 )  
	BEGIN  
	  SET @InsertString += ' VALUES ( @StmtParamUserId, @StmtParamUserNm, 30 );' 
	END 
	ELSE 
	BEGIN
	  SET @InsertString += ' VALUES ( @StmtParamUserId, @StmtParamUserNm, 15 );'  
	END                

	SET @ParmDefinition =  N'@StmtParamUserId    INT,
							@StmtParamUserNm    VARCHAR(100)
							
					 		';
	 --  SELECT문 사용해서 프로시저 디버깅 하기위함 입니다.
	SELECT 'InsertString : ' + @InsertString;

	EXEC sp_executesql @InsertString,  
			@ParmDefinition,  
			@ParamUserId, 
			@ParamUserNm 
END

--  소스 실행
EXECUTE DBO.STORE_PROCEDURE_DEBUGGING_SELECT 10, 'DEBUGGING_SELECT',24

RAISERROR 디버깅 예제.

RAISERROR는 TRY CATCH 문하고 사용합니다.
RAISERROR 소스 예시입니다.

-- RAISERROR 디버깅 예제.
-- DROP PROCEDURE [dbo].[STORE_PROCEDURE_DEBUGGING_RAISERROR]
CREATE PROCEDURE [dbo].[STORE_PROCEDURE_DEBUGGING_RAISERROR]   
    @UserAge NVARCHAR(10) 
AS
BEGIN
BEGIN TRY
    
	SELECT UserAge / @UserAge  AS ' ERROR ' 
	FROM UserInfoForDebugging;

	END TRY
	BEGIN CATCH
		RAISERROR('Error occurred', 10, 1);  
	END CATCH

END;
GO
-- RAISERROR  ERROR
EXECUTE DBO.[STORE_PROCEDURE_DEBUGGING_RAISERROR] @UserAge = 0;
GO
-- NO ERROR --> OK 
EXECUTE DBO.[STORE_PROCEDURE_DEBUGGING_RAISERROR] @UserAge = 1;

DEBUG FLAG 이용한 디버깅 예제.

DEBUG FLAG 통한 디버깅은 저장 프로시저를 생성할 때부터 디버깅을 위한 매개 변수가 필요합니다.
DEBUG FLAG 통한 디버깅은 원본 소스를 수정하지 않고 매개변수 값을 변경하면서 디버깅할 수 있습니다.
DEBUG FLAG 소스입니다.

-- DEBUG FLAG 이용한 디버깅 예제.
-- DROP PROCEDURE [dbo].[STORE_PROCEDURE_DEBUGGING_DEBUG_FLAG];
CREATE PROCEDURE [dbo].[STORE_PROCEDURE_DEBUGGING_DEBUG_FLAG] 
  @ParamUserId    INT
 ,@ParamUserNm    VARCHAR(100)
 ,@ParamUserAge   INT 
 ,@Debug_Flag     VARCHAR(1)
 
AS  
BEGIN 
	DECLARE @InsertString NVARCHAR(500);
	DECLARE @ParmDefinition NVARCHAR(500);  
  
	SET @InsertString = ' INSERT INTO  dbo.UserInfoForDebugging ' 
					+ ' ( UserId,UserNm,UserAge ) ';
 						 
	IF(@ParamUserAge > 20 )  
	BEGIN  
	SET @InsertString += ' VALUES ( @StmtParamUserId, @StmtParamUserNm, 30 );' 
	END 
	ELSE 
	BEGIN
	SET @InsertString += ' VALUES ( @StmtParamUserId, @StmtParamUserNm, 15 );'  
	END                

	SET @ParmDefinition =  N'@StmtParamUserId    INT,
							@StmtParamUserNm    VARCHAR(100)							
					 		';

	IF(@Debug_Flag = 'Y')		 
		SELECT 'InsertString : ' + @InsertString;		  
	ELSE 		 
		EXEC sp_executesql @InsertString,  
				@ParmDefinition,  
				@ParamUserId, 
				@ParamUserNm 
END

--  소스 실행
EXECUTE [dbo].[STORE_PROCEDURE_DEBUGGING_DEBUG_FLAG] '198','DEBUG_FLAG','10','Y'