[SQL] 동적쿼리

금번 작업을 하면서 고민을 하게 만드는 요청을 받았다.

 

요청 사항을 정리하면 

 

1. 정렬 DB에서 한다.

1-1. 정렬할 대상(컬럼)은 서버로 부터 전달 받는다.

1-2. 정렬할 대상(컬럼)에 대해서 오름/내림 차순에 대한 결정도 서버로 부터 받는다.

 

2. 일정 갯수만을 반환한다.

2-1. 페이지 개념을 적용한다. 

2-2. 정렬된 값에서 요청 받은 페이지의 약속된 열만큼을 반환해야 한다.

 

※ 이 글을 읽는 분들은 더 좋은 해답이 있다면 언제든 조언을 부탁드립니다.

 

일단 서버로 부터 받는 인자는

 @정렬기준(대상컬럼)   TINYINT,

 @정렬방법(오름/내림) TINYINT,

 @페이지크기 INT,

 @페이지넘버 INT

를 받는다.

 

동적쿼리는 문자열을 만들어서 그것을 실행(EXEC) 하는 것이기 때문에 지역 변수들은 

NVARCHAR(크기)로 적당한 크기로 잡아 둔다. 

 

DECLARE

 @쿼리 NVARCHAR(적당한크기),

 @정렬 NVARCHAR(적당한크기),

 @방법 NVARCHAR(적당한크기),

 @최소 NVARCHAR(적당한크기), 

 @최대 NVARCHAR(적당한크기),

 

일단.

1) 정렬기준

 SELECT @정렬 = CASE WHEN @정렬기준 = 1 THEN '이름'

                                   WHEN @정렬기준 = 2 THEN '나이' END;

 이렇게 정렬을 문자로 변환할 수 있다.

 

2) 정렬방법

 SELECT @방법 = CASE WHEN @정렬방법 = 1 THEN 'ASC'

                                   WHEN @정렬방법 = 2 THEN 'DESC' END;

 

3) 페이지 범위.

 SELECT @최소 =  CONVERT(NVARCHAR(적당한크기), (@페이지넘버 - 1) * (@페이지크기 + 1));

 SELECT @최대 =  CONVERT(NVARCHAR(적당한 크기), (@페이지넘버 * @페이지크기));

 

4) 쿼리 문자로 만들기.

 SET @쿼리 = N'

    SELECT D.* FROM 

                    ( SELECT ROW_NUMBER() OVER(ORDER BY C.' + @정렬 + ' ' + @방법 + ') 

                AS ROW, C.* 

FROM 

(SELECT 

A.기부금, B.이름, B.나이

FROM 고객추가정보 A

LEFT JOIN 고객정보 B

ON A.고객번호 = B.고객번호) AS C ) AS D

WHERE D.ROW 

        BETWEEN ' + @최소 + ' AND ' + @최대 + 'ORDER BY D.' + @정렬 + ' ' + @방법;

 

이렇게 하면 @쿼리에 담기게 된다.

 

5) 마무리

 

마지막에 다음의 문장을 넣어주면 끝난다.

EXEC (@쿼리);

 

하지만 그전에 완성된 쿼리가 궁금하다면 

--EXEC (@쿼리); 

PRINT @쿼리; 

이렇게 해보고 나서 하는게 좋지 않을까?

 

그거 쫌 적는다고 힘드네.