[2k5] CASE문을 이용한 동적쿼리의 정적쿼리 변환

/********************************************************************************************
-- Title : [2k5] CASE문을 이용한 동적쿼리의 정적쿼리 변환
-- Reference : 필라넷 김정선님, http://dbrang.tistory.com/263
-- Key word : 동적쿼리 정적쿼리 CASE
********************************************************************************************/
USE AdventureWorks;
GO

-- WHERE 절에서의 동적 쿼리
DECLARE @COLUMN VARCHAR(10), @VALUE VARCHAR(20);
SET @COLUMN = 'first';
SET @VALUE = 'b%';     
  

SELECT *
FROM Person.Contact
WHERE (CASE @COLUMN WHEN 'Last' THEN LastName
                    WHEN 'First' THEN FirstName
                    WHEN 'Title' THEN Title
                    ELSE @VALUE
       END
      ) LIKE @VALUE;

-- SELECT 절에서의 동적 쿼리
DECLARE @COLUMN VARCHAR(10);
SET @COLUMN = 'title';

SELECT ContactID
     , CASE @COLUMN WHEN 'Name' THEN LastName
                    WHEN 'Title' THEN Title
                    ELSE LastName
       END "COLUMN1"
     , CASE @COLUMN WHEN 'Name' THEN FirstName
                    WHEN 'Title' THEN LastName
                    ELSE CONVERT(NCHAR(10), modifieddate, 112)
       END "COLUMN2"
     , CASE @COLUMN WHEN 'Title' THEN CONVERT(NCHAR(10), ModifiedDate, 112)
                    ELSE ''
       END "COLUMN3"
FROM Person.Contact;

-- ORDER BY 절에서의 동적 쿼리
DECLARE @OrderBy VARCHAR(10);
SET @OrderBy = 'Fisrt';

SELECT *
FROM Person.Contact
ORDER BY (CASE @OrderBy WHEN 'Last' THEN LastName
                        WHEN 'First' THEN FirstName
                        WHEN 'Title' THEN Title
          END
         ) ASC;

-- ORDER BY 절에서의 동적 쿼리 2
DECLARE @OrderBy VARCHAR(10), @SEQUENCE VARCHAR(4);
SET @OrderBy = 'LastName';
SET @SEQUENCE = 'DESC';

SELECT *
FROM Person.Contact
ORDER BY (CASE @SEQUENCE WHEN 'ASC'
                         THEN CASE @OrderBy WHEN 'LastName' THEN LastName
                                            WHEN 'Title' THEN Title
                              END
          END
         ) ASC
       , (CASE @SEQUENCE WHEN 'DESC'
                         THEN CASE @OrderBy WHEN 'LastName' THEN LastName
                                            WHEN 'Title' THEN Title
                              END
          END
         ) DESC;

-- ORDER BY 절에서의 CASE를 활용안  ASC/DESC 처리

select a.skey, row_number() over(order by (case when @ord_sect = 'ASC' then a.appl_dt end) ASC

                                        , (case when @ord_sect = 'DESC' then a.appl_dt end) DESC

                                        , skey ASC

                                ) "ttt"

     , a.appl_dt

from 

(

    Subquery

) a ;

-- 나머지는 http://dbrang.tistory.com/263 에서 확인.