동적쿼리

2012. 4. 3. 11:22·WEB TIP/MS-SQL

출처:  http://symplog.tistory.com/363 


어쩔 수 없이 동적 쿼리를 사용하다가 보면 가끔씩 아무 생각없이 넘어가는 부분이 많이 있습니다. 
이런 몇가지 부분을 고민해서 해결한다면 좋은 성능과 유지보수 용이성을 가진 쿼리를 만들 수 있습니다. 

1.  SQL Injection 

 - 동적 쿼리를 만들다 보면 흔히 고민하게 되는 부분입니다. 명확한 파라미터 타입 사용, char 타입의 사용시에는 꼭 필요한 길이만, 그리고, 마지막으로 변수값에 대한 필터링이 필요하겠습니다. 

2.  Like 문
 - 보통 Like 를 사용할때 문장을 연결하는 경우가 있습니다.
 
 set @strSQL = ' select top 10 * from mt_song_tmp where song_name like ''' + @likeParam + '%'' '

   이렇게 바꿀 수 있습니다. 
   set @strSQL = ' select top 10 * from mt_song_tmp where song_name like @likeParam ' <-- sp_executesql 을 사용해야 합니다.

   위의 두 쿼리는 파라미터 값만 변경하면 됩니다. 첫번 것은 보통 파라미터 값이 '물건' 이렇게 들어온다면 수정한 문장에서 사용되어 지는 파라미터 값은 '물건%' 라고 하면 되겠지요.

3. 디버깅
 - 동적쿼리는 디버깅 하기가 좀 귀찮은 면이 있습니다. 그렇지만 간단한 파라미터 하나로 디버깅을 할 수 있도록 하면 유지보수성이 좋아집니다. #debug 같은 느낌이랄까요.
  create proc dbo.UP_GetProductInfo
    @likeParam varchar(100),
    @debug bit = 0
  as 
  .......

 if @debug = 1
   print @likeParam -- or 실행될 문장
 else
   exec sp_executesql -- 직접 실행 

4. 유지보수
 - 가끔씩 어떤 테이블을 변경하면서 관련된 쿼리를 찾아야 하는 경우가 발생합니다. 이럴때는 sp_depends 를 사용하게 되는데요. 동적쿼리를 사용하다보면 이 녀석들은 안나타나게 됩니다. 특히나 테이블명을 동적으로 변경하는 쿼리들은 알수가 없게 됩니다. (DMV 쿼리 구문으로도 찾기 어려운 경우가 있습니다.) 
이럴때를 대비해서 아래와 같이 가볍게(?) 하나 넣어주게 되면 향후 유지보수성이 좋아지게 됩니다.

if 1 = 2
begin
  select top(0) 0 from mt_song_tmp;
end 


이상 몇가지를 정리해봤습니다. 

동적쿼리는 구현해야하는 동기 만큼이나 귀찮은 녀석입니다. 조금만 더 신경쓴다면 시스템을 장애로 밀어넣는 훌륭한(?) 품질의 쿼리를 생산하지 않으리라 생각됩니다. 향후 유지보수에도 큰 도움이 될 것입니다.

대략적인 쿼리의 모습은 다음과 같습니다.

create proc dbo.UP_GetSongINFO

           @likeParam varchar(100),

           @debug bit = 0

as

 

set nocount on

 

declare @strSQL nvarchar(2000)

declare @paramDefinition nvarchar(1000)

 

set @strSQL = 'select * from mt_song_tmp where song_nm like @likeParam'

 

set @paramDefinition = '@likeParam varchar(100)'

 

if @debug = 1

begin

           print @likeParam

end

else

           exec sp_executesql @strSQL, @paramDefinition, @likeParam

 

 

if 1 = 2

begin

           select top (0) 0 from mt_song_tmp with (nolock)

end

'WEB TIP > MS-SQL' 카테고리의 다른 글

프로시저 실행일자, 실행횟수  (0) 2013.12.31
프로시저, 함수, 트리거 생성 정보 보기.  (0) 2013.12.20
[MS-SQL] SQL Server 2005의 데이터 형식  (0) 2011.09.14
Transaction  (0) 2010.01.07
Plan Caching  (0) 2010.01.06
'WEB TIP/MS-SQL' 카테고리의 다른 글
  • 프로시저 실행일자, 실행횟수
  • 프로시저, 함수, 트리거 생성 정보 보기.
  • [MS-SQL] SQL Server 2005의 데이터 형식
  • Transaction
제프
제프
제프
ZEP@BLOG
제프
전체
오늘
어제
  • 분류 전체보기 (193)
    • 주저리 (4)
    • WEB TIP (5)
      • 웹뉴스 (1)
      • UI (3)
      • ASP (35)
      • HTML-CSS (5)
      • jQuery (7)
      • C# ,ASP.NET (29)
      • MS-SQL (40)
      • Cloud (1)
      • Oracle (24)
      • Exchange 2003 (3)
      • Windows Server (9)
      • 용어 (1)
      • IIS (4)
      • 기타 (1)
      • Redmine (3)
    • 영어 (7)
    • 경제.경영자료 (10)

블로그 메뉴

  • 홈
  • 태그
  • 미디어로그
  • 위치로그
  • 방명록

공지사항

인기 글

태그

  • 영어
  • injection
  • MS-SQL
  • IIS
  • 인덱스
  • Windows2003
  • SQL
  • 숙취
  • 오라클
  • Git
  • jquery
  • c#
  • Convert
  • case
  • ASP.NET
  • 암호화
  • ADO
  • oracle
  • asp
  • SEQUENCE

최근 댓글

최근 글

hELLO· Designed By정상우.v4.5.2
제프
동적쿼리
상단으로

티스토리툴바

단축키

내 블로그

내 블로그 - 관리자 홈 전환
Q
Q
새 글 쓰기
W
W

블로그 게시글

글 수정 (권한 있는 경우)
E
E
댓글 영역으로 이동
C
C

모든 영역

이 페이지의 URL 복사
S
S
맨 위로 이동
T
T
티스토리 홈 이동
H
H
단축키 안내
Shift + /
⇧ + /

* 단축키는 한글/영문 대소문자로 이용 가능하며, 티스토리 기본 도메인에서만 동작합니다.