[MS-SQL] 검색 조건을 파라미터로 받을 때
bbs란 테이블에 id, author, subject, context 라는 컬럼이 존재하고 WHERE절의 검색조건을 파라미터로 받을 때, 그 값이 있을 수도 있고 없을 수도 있다고 가정하고 시작한다.
CASE문을 적용한 쿼리는:
SELECT id, author, subject, content
FROM bbs
WHERE subject like '%'
+ CASE
WHEN @subject IS NOT NULL THEN @subject
WHEN @subject != '' THEN @subject
ELSE ''
END
+ '%'
@subject가 null이거나 empty string 일 때의 WHERE절은 subject like '%' + '' + '%'이며 모든 데이터가 출력된다.
CASE를 ISNULL() 함수로 바꾸면:
SELECT id, author, subject, content
FROM bbs
WHERE ISNULL(@subject, '') = ''
OR subject LIKE '%' + @subject + '%'
@subject가 null이거나 empty string일 때의 WHERE절은:
WHERE ' ' = ' '
OR subject LIKE '%' + ' ' + '%'
이며 이 경우 선행 비교 조건(OR은 앞조건이 true일 때 뒤의 조건은 무시한다)이 모든 레코드에 TRUE이므로 뒤의 조건은 비교하지 않는다.
반면 @subject가 값이 있을 경우엔, 예를 들어 파라미터가 '가나다라'로 들어왔을때 WHERE 절은:
WHERE '가나다라' = ' '
OR subject LIKE '%' + '가나다라' + '%'
이 경우 선행 비교 조건은 모든 레코드에 FALSE 이므로 뒤의 조건을 적용한 결과를 출력하게 된다.
만약 NVL()을 사용한다면 '' = '' 대신 1 = 1(결과가 true인 비교식이면 상관없음. 단 오라클의 경우, 데이터 타입에 주의할 것. 가령 파라미터가 숫자일때 'a' = 'a'는 에러를 발생한다. 특이하게도 '1' = '1'은 모든 숫자와 문자 타입 모두 대응한다) 로 작성한다. 그리고 NVL함수는 빈문자열도 NULL(MS-SQL에선 '' != null 이며 '' = ''는 true이지만 oracle은 '' == null 이며 '' = '' 는 false다.)로 취급한다.
SELECT *
FROM regions
WHERE NVL(파라미터, '1') = '1'
OR region_id = 파라미터
위의 조건이 있을 때 실제 쿼리는:
WHERE NVL(2, '1') = '1'
OR region_id = 2
조건이 없을 때는:
WHERE NVL('', 1) = 1 OR region_id = ''
가 된다.
끗