디비랑[dɪ'bɪraŋ]

/********************************************************************************************
-- Title : [2k5] 검색 조건이 다양한 경우 동적쿼리 회피
-- Key word : 동적쿼리 정적쿼리 검색 조건 case
********************************************************************************************/
-- ERP 등에서 검색 항목이 많은데 사용자가 원하는 검색만 조건에 들어가는 경우
-- 처리하기...

-- Case 1. (초간단)
DECLARE @ctgr_cd NCHAR(2), @ti NVARCHAR(100), @brd_seq INT;
--select @ctgr_cd = '--', @ti = '--', @brd_seq = 99999
SELECT @ctgr_cd = 'N3', @ti = '%안내%', @brd_seq = 1
--select @ctgr_cd = '--', @ti = '%안내%', @brd_seq = 99999

SELECT *
FROM sch.tbl WITH (NOLOCK)
WHERE (ctgr_cd = @ctgr_cd OR @ctgr_cd = '--')
AND (ti LIKE @ti OR @ti LIKE '--')
AND (brd_seq = @brd_seq OR @brd_seq LIKE 99999);

 

-- Case 2. (간단)
DECLARE @ctgr_cd NCHAR(2), @ti NVARCHAR(100), @brd_seq INT;
SELECT @ctgr_cd = '--', @ti = '--', @brd_seq = 99999
SELECT @ctgr_cd = 'N3', @ti = '%안내%', @brd_seq = 1

SELECT *
FROM sch.tbl WITH (NOLOCK)
WHERE
    (CASE WHEN @ctgr_cd = '--' THEN '--'
         ELSE ctgr_cd
     END) = @ctgr_cd
AND (CASE WHEN @ti = '--' THEN '--'
          ELSE ti
     END) LIKE @ti
AND (CASE WHEN @ti = '--' THEN '--'
          ELSE ti
     END) LIKE @ti
AND (CASE WHEN @brd_seq = 99999 THEN 99999
          ELSE brd_seq
     END) = @brd_seq;
     
         
-- Case 3. (복잡)
DECLARE @ctgr_cd NCHAR(2), @ti NVARCHAR(100), @brd_seq INT;
--select @ctgr_cd = '--', @ti = '--', @brd_seq = 99999
--select @ctgr_cd = 'N3', @ti = '%안내%', @brd_seq = 1
select @ctgr_cd = '--', @ti = '%안내%', @brd_seq = 99999

SELECT *
FROM sch.tbl WITH (NOLOCK)
WHERE (CASE WHEN @ctgr_cd <> '--' THEN ctgr_cd
           ELSE '1'
       END) = (CASE WHEN @ctgr_cd <> '--' THEN @ctgr_cd
                    ELSE '1'
               END)
AND (CASE WHEN @ti <> '--' THEN ti
          ELSE '1'
     END) LIKE (CASE WHEN @ti <> '--' THEN @ti
                     ELSE '1'
                END)
AND (CASE WHEN @brd_seq <> 99999 THEN brd_seq
          ELSE 1
     END) = (CASE WHEN @brd_seq <> 99999 THEN @brd_seq
                  ELSE 1
             END);