[MSSQL] 동적 쿼리로 정렬을 주는 방법.

동적쿼리 또는 프로시저를 통해 정렬을 위한 파라메터를 넘겨서 정렬을 하는 방법...

구글에서 대충 검색어 "stored procedure parameter sort order" 요놈으로 찾아보니,

다음의 링크가 나옵니다.

http://www.4guysfromrolla.com/webtech/010704-1.shtml

결론은, CASE 문을 잘 활용하여 정렬을 하면 된다는 것인데, 이에 착안하여, CASE 문을 SELECT 절로 빼 내고, PATINDEX 함수를 활용하여 컴마 값으로 값을 쪼개어(Split) 여러 컬럼에 대한 정렬까지 주는 프로시저를 만들어 보았습니다.

혹시 필요하신 분들은 퍼가세요.

/* 여기에서 부터 */
CREATE TABLE TBL_TEST
(
    CD INT,
    NM NVARCHAR(100),
    DC NVARCHAR(100)
);

INSERT INTO TBL_TEST ( CD, NM, DC ) VALUES ( 0, '이순신', '김유신' );
INSERT INTO TBL_TEST ( CD, NM, DC ) VALUES ( 1, '홍길동', '강감찬' );
INSERT INTO TBL_TEST ( CD, NM, DC ) VALUES ( 2, '이효리', '크리스찬베일' );
INSERT INTO TBL_TEST ( CD, NM, DC ) VALUES ( 3, '홍길동', '강감찬' );
INSERT INTO TBL_TEST ( CD, NM, DC ) VALUES ( 4, '전두환', '이명박' );
INSERT INTO TBL_TEST ( CD, NM, DC ) VALUES ( 5, '박근혜', '이명박' );
INSERT INTO TBL_TEST ( CD, NM, DC ) VALUES ( 6, '노무현님', '이명박' );
INSERT INTO TBL_TEST ( CD, NM, DC ) VALUES ( 7, '유재석', '노홍철' );
INSERT INTO TBL_TEST ( CD, NM, DC ) VALUES ( 8, '유재석', '노홍철' );
INSERT INTO TBL_TEST ( CD, NM, DC ) VALUES ( 9, '유재석', '강감찬' );
/* 여기까지는 테스트를 위한 데이터 임 */

CREATE PROCEDURE USP_DYNAMIC_SORT
(
    @SORT_STR  NVARCHAR(10) -- CD, NM, DC ...
)
AS
BEGIN

    DECLARE @P_IDX   INT,
            @P_TEMP  NVARCHAR(2),
            @P_SORT1 NVARCHAR(2),
            @P_SORT2 NVARCHAR(2),
            @P_SORT3 NVARCHAR(2);
            
    SET @P_IDX = 1;
            
    WHILE LEN(@SORT_STR) > 0
    BEGIN
        IF PATINDEX('%,%', @SORT_STR) > 0
        BEGIN
            SET @P_TEMP = SUBSTRING(@SORT_STR, 0, PATINDEX('%,%', @SORT_STR));
            SET @SORT_STR = SUBSTRING(@SORT_STR, LEN(@P_TEMP + '|') + 1, LEN(@SORT_STR));
        END
        ELSE
        BEGIN
            SET @P_TEMP = @SORT_STR
            SET @SORT_STR = NULL
        END;
        
        IF @P_IDX = 1
            SET @P_SORT1 = LTRIM(RTRIM(@P_TEMP));
        ELSE IF @P_IDX = 2
            SET @P_SORT2 = LTRIM(RTRIM(@P_TEMP));
        ELSE IF @P_IDX = 3
            SET @P_SORT3 = LTRIM(RTRIM(@P_TEMP));
        
        SET @P_IDX = @P_IDX + 1;
    END;
    
    PRINT @P_SORT1;
    PRINT @P_SORT2;
    PRINT @P_SORT3;

    SELECT T.CD,
           T.NM,
           T.DC
      FROM ( SELECT CD,
                    NM,
                    DC,
                    CASE @P_SORT1 WHEN 'NM' THEN NM
                                  WHEN 'DC' THEN DC
                                  ELSE CONVERT(NVARCHAR, CD) END AS SORT_COL1,
                    CASE @P_SORT2 WHEN 'NM' THEN NM
                                  WHEN 'DC' THEN DC
                                  ELSE CONVERT(NVARCHAR, CD) END AS SORT_COL2,
                    CASE @P_SORT3 WHEN 'NM' THEN NM
                                  WHEN 'DC' THEN DC
                                  ELSE CONVERT(NVARCHAR, CD) END AS SORT_COL3
               FROM TBL_TEST
           ) T
     ORDER BY T.SORT_COL1, T.SORT_COL2, T.SORT_COL3;
END;

/* 실행해 본다. */
EXEC USP_DYNAMIC_SORT @SORT_STR='NM';
EXEC USP_DYNAMIC_SORT @SORT_STR='DC,CD';

/* 만들었던 거 드랍 */
DROP PROCEDURE USP_DYNAMIC_SORT;
DROP TABLE TBL_TEST;

각각의 결과는 다음과 같습니다.

자 그럼, 이 프로시저의 문제점은 뭘까요?

네, 뭐 문제점이야 수두룩하지만, 일단 제가 바로 찾은 문제는 뭐냐하면, CD 컬럼은 숫자형이고 다른 컬럼은 문자열입니다. 다들 아시다시피, 숫자로서의 정렬은, 3 보다 10 이 큰 값 이지만, 문자로서 정렬할 때에는 3 이 10 보다 큰 값 입니다. 예를 들어 1부터 10까지 순차적으로 나열하면, 숫자일 때에는 1,2,3,4,5,6,7,8,9,10 이고, 문자일 때에는 1,10,2,3,4,5,6,7,8,9 가 됩니다. 이를 보완하기 위해서는 숫자컬럼 값 일 때에는 REPLICATE 등의 함수를 통해 좌측을 0으로 채우고, 다시 SUBSTRING 이나 RIGHT 함수를 사용하여 넣어줘야 합니다. (ex, 001,002,003,004,005,006,007,008,009,010...)

그러한 작업은 현업에 맞추어 최대치를 잡아 해 주면 되리라고 봅니다.

(그나저나, 적용한 syntax highlighting 맘에 안 드네요 좀.)