[DB] mssql 프로시저 동적쿼리 생성방법

@sincerely-media-rct82Ei80Fw unsplash

[DB] mssql 프로시저 동적 쿼리 생성 방법

 

MSSQL에서 프로지 서로 동적 쿼리를 생성해야 되는 일이 있었습니다.

그래서 한번 정리 삼아 작성해보았습니다.

간단하게 설명을 하자면 쿼리 내 검색조건 @WHERE 파라미터에 따라서 쿼리가 변경됩니다.

 

파라미터에 쿼리를 더하는 방법으로는 SET @where = @where + ~ 또는 SET @where += ~ 이런 식으로 두 가지 방법으로 할 수 있습니다.

SET @where = @where + 'AND A.typecd = ''' + @p_typecd + ''''

또는

SET @where += 'AND A.typecd = ''' + @p_typecd + ''''  

 

DECLARE @Sql                    NVARCHAR(MAX)
DECLARE @ParameterDifinition    NVARCHAR(MAX)
DECLARE @where                  NVARCHAR(2000)

SET @p_returnvalue = 'Success'
SET @p_returnmessage = ''
SET @p_count = 0
SET @ParameterDifinition  = N'
   @p_pageno    INT
 , @p_max       INT
 , @p_count     INT OUTPUT'

SET @Where = ' WHERE 1=1  '  

IF @p_typecode <> ''
BEGIN  
	SET @Where = @Where + ' AND A.typecd = '''+ @p_typecode + '''' 
END

IF @p_searchtype <> ''
BEGIN  
	IF @p_keyword <> ''
	BEGIN
	SET @Where = @Where + ' AND A.'+ @p_searchtype + ' like ''%'+@p_keyword + '%''' 
	END
END  

SET @Sql = N'
	SELECT   
		  LIST.RowNum
	    	, LIST.seq
		, LIST.email
		, LIST.username
        	, LIST.deptname		   
		, LIST.typecd
		, LIST.regdate
	FROM  
	(
		SELECT 
				  ROW_NUMBER() OVER (ORDER BY A.seq DESC) AS RowNum  
				, A.[seq]
				, A.[email]
				, TRIM(A.[username]) as username
				, A.[deptname]
				, A.[typecd]												
				, convert(varchar(19),isnull(A.[moddate], A.[regdate]) ,120) as regdate
		FROM dbo.tb_test A
		' + @Where + ' 
	)LIST
	WHERE List.RowNum BETWEEN ((@p_pageno-1) * @p_max+1) AND ((@p_pageno*@p_max))  
	ORDER BY List.RowNum ASC'

	SELECT 
		@p_count = COUNT(A.seq)
	FROM dbo.tb_test A
	
EXEC SP_EXECUTESQL @Sql